tag:blogger.com,1999:blog-10550791Mon, 06 Apr 2026 20:11:06 +0000Scriptssql server 2008Learn SQL Server 2005ArchitecturedmvtsqlKatmaiArt of SQL ServerInstallationindex fragmentationsys.dm_db_index_physical_statsupdate statisticsFree SQL Server toolsMaintenance PlanReset Identity columnSPIDasynchronous mirroringcsvfind outdated Statisticsoutdated Statisticssys.dm_exec_connectionsFree SQL Server Articles and Scripts. *New: Learn sql server 2005 Serieshttp://sequelserver.blogspot.com/noreply@blogger.com (Aneesh)Blogger255125en-usnoFree SQL Server Articles and Scripts. *New: Learn sql server 2005 SeriesFree SQL Server Articles and Scripts. *New: Learn sql server 2005 Seriesnoreply@blogger.comtag:blogger.com,1999:blog-10550791.post-3366413549304313707Tue, 01 Sep 2015 18:57:00 +00002015-09-01T11:57:23.614-07:00Microsoft SQL Server Licensing – abstract &#160; Terms used: An OSE is an Operating System Environment. A POSE is a Physical Operating System Environment. A VOSE is a Virtual Operating System Environment. General rules SQL Server licenses are assigned only to physical servers. The number of SQL Server instances in an OSE is unlimited. A physical OSE is counted as http://sequelserver.blogspot.com/2015/09/microsoft-sql-server-licensing-abstract.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-4839643178193196236Mon, 27 Jul 2015 17:45:00 +00002015-07-27T10:45:10.735-07:00I always set this value to CHECKSUM. When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This http://sequelserver.blogspot.com/2015/07/what-best-pageverify-setting.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-917662597565588780Mon, 27 Jul 2015 17:44:00 +00002015-07-27T10:44:37.014-07:00SQL Server is well known for being a memory hog. When installed, the default configuration allocates a ridiculous amount of memory (2147483647 MB) to the instance. In effect, this grants SQL Server access to whatever memory is assigned to the server, which it will try to use at times at the expense of operating system tasks. Best practice is therefore to reduce the memory allocated to the http://sequelserver.blogspot.com/2015/07/sql-max-memory-limit-too-low-increase.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-3560879296807960067Tue, 24 Mar 2015 20:41:00 +00002015-03-24T13:41:40.582-07:00Most of the novice dba’s forget to take the transaction log backups, which eventually ends up consuming a lot of disk space. This is one of the questions people ask me very often. Here are the steps which I tell them to follow. Take the full backup if the database Check to see if something is keeping SQL from reusing existing log space: SELECT name, log_reuse_wait_desc FROM http://sequelserver.blogspot.com/2015/03/how-to-recover-space-from-huge.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-4859653890093239182Tue, 24 Mar 2015 20:41:00 +00002015-03-24T13:41:27.350-07:00To understand what this is doing, first we need to understand what Nul is, in the context of a file. It is not just a misspelling of NULL. DOS, since the earliest days, has had a number of devices that can be treated like files, eg LPT1, COM1 and CON. NUL is another of those devices (virtual files, if you like) and is equivalent to the unix \dev\nul, a ‘file’ that deletes any data that’s writtenhttp://sequelserver.blogspot.com/2015/03/backup-database-to-disk-nul.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-8279774615931373787Tue, 24 Mar 2015 20:41:00 +00002015-03-24T13:41:10.858-07:00This is one of the issues the consultant faces, when there client has little documentation on the systems and the databases they use.&#160; The first step in this process is simply looking at the date &amp; time stamps of the .MDF and .LDF files, which could help in some cases,To evaluate whether it was worth keeping the databases online, I had some investigation to do. Step 1 : Who is http://sequelserver.blogspot.com/2015/03/identifying-unused-databases.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-6734360830909198662Tue, 24 Mar 2015 20:40:00 +00002015-03-24T13:40:38.617-07:00Here are the main differences you should know about these three date types: &#160; Range of Dates Accuracy Size usage Applies to smalldatetime January 1, 1900 - June 6, 2079 one Minute. smalldatetime values with 29.998 seconds or lower are rounded down to http://sequelserver.blogspot.com/2015/03/difference-between-datetime2-datetime.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-2929133333026843733Tue, 24 Mar 2015 20:40:00 +00002015-03-24T13:40:28.062-07:00Some body have this error : Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter. When i execute my maintenance plan. &#160; Resolution The issue is related to the default email profile, you have to set a default email profile in order for the maintenance http://sequelserver.blogspot.com/2015/03/sql-maintenance-plan-error-could-not.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-8929182519823112306Mon, 02 Dec 2013 22:38:00 +00002013-12-02T14:38:50.444-08:00&#160; Here is the work around to resolve this error (Works on SQL 2008, SQL 2012 ) Click Start -&gt; Run -&gt; dcomcnfg Component services –&gt; computers –&gt; my computer –&gt;DCOM config-&gt;MSDTSServer100 &#160;&#160;&#160; Right click and select Properties &#160;&#160;&#160; Security &#160;&#160;&#160; Launch and Activate permissions – edit Add the local SQL AgentUser and http://sequelserver.blogspot.com/2013/12/sql-agent-dcom-errors-event-id-10016.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-1758160347908921237Thu, 03 Oct 2013 17:21:00 +00002013-10-03T10:21:06.441-07:00Someone asked me this question and it took me a while to figured out the solution. It look very simple with the new features available in SQL Server 2008. Here is the example &#160; &#160; if object_id('tempdb..#temp') is not null drop table #temp create table #temp([Name] varchar(10),[m1] int,[m2] int,[m3] int,[m4] int) insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34, 36, http://sequelserver.blogspot.com/2013/10/how-to-select-max-row-value-using-tsql.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-4775051260739149795Tue, 01 Oct 2013 15:09:00 +00002013-10-01T08:09:34.440-07:00Here is a very useful script that shows the backup history. &#160; SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, s.backup_start_date, s.backup_finish_date, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CASThttp://sequelserver.blogspot.com/2013/10/sql-server-scriptbackup-history.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-1228460805280189072Wed, 25 Sep 2013 16:08:00 +00002013-09-25T09:08:06.453-07:00When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to ahttp://sequelserver.blogspot.com/2013/09/pass-variable-to-linked-server-query.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-6578357002367071936Wed, 18 Sep 2013 19:21:00 +00002013-09-18T12:21:34.429-07:00Today I am starting my series on ‘What’s new in SQL 2012’ ; One of the interesting functions available is ‘CHOOSE’ which can be used to achieve some of the features of ‘CASE’ statement Syntax CHOOSE ( index, val_1, val_2 [, val_n ] )Arguments indexIs an integer expression that represents a 1-based index into the list of the items following it.If the provided index value has a numeric data http://sequelserver.blogspot.com/2013/09/sql-server-2012choose-function.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-8724839036072910357Tue, 10 Sep 2013 16:09:00 +00002013-09-10T09:09:30.015-07:00This happened after doing and in-place upgrade to our existing SQL 2008R2 server. Happened only on one of our database which was on Simple recovery. It look strange to me when the SHRINKFILE command failed. When i compared the logical names on sys.master_files and sys.database_files, the both look different, I try to rename them back to the same file which of course didn't worked , so i thought http://sequelserver.blogspot.com/2013/09/msg-8985-level-16-state-1-line-1-could.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-7755987901140007807Fri, 06 Sep 2013 18:14:00 +00002013-09-06T11:14:44.510-07:00Here is the in place upgrade checklist I created for migrating from SQL 2008R2 Pre upgrade Checklist Make sure the hardware requirements are satisfied; use System Configuration Checker (SCC) that comes part of sql server setup to verify SQL Server 2012 requires Windows Server 2008 R2 SP1 or later Analyze the legacy sql server using SQL server 2012 Upgrade advisor Ensure that SQL http://sequelserver.blogspot.com/2013/09/sql-server-2012-upgrade-check-list-non.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-2694122178773518240Thu, 15 Aug 2013 16:42:00 +00002013-08-15T09:45:45.620-07:00ArchitecturedmvLearn SQL Server 2005Scriptssql server 2008sys.dm_db_index_physical_statstsqlAs most of you know, an index can improve the performance of a query most of the time; internally sql server has to do a lot of work to maintain those indexes. When I started a new job, I came to see a lot of indexes were created for some of our production tables. So I decided to find a script that returns the indexes that’s not in use. Note : it uses the dmv’s so, this is not a pretty accuratehttp://sequelserver.blogspot.com/2013/08/sql-server-unused-index-script.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-4310048779791895009Wed, 17 Jul 2013 16:46:00 +00002013-07-17T14:49:06.418-07:00One of my client was experiencing this strange error while trying to logon to the sql server using windows authentication, he tried logging in from different machines still got the same error. &#160; &quot;ProviderNum: 7, Error: 10054, ErrorMessage: &quot;TCP Provider: An existing connection was forcibly closed by the remote host ...&quot; To resolve this problem, increase the MaxTokenSize http://sequelserver.blogspot.com/2013/07/error-17832-login-packet-used-to-open.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-8720186223276872052Wed, 10 Jul 2013 15:43:00 +00002013-07-10T08:43:07.939-07:00As a dba, several guys asked me this question on many occasions. All they want to do is free up some space from the transaction log file , and while running the DBCC SHRINKFILE() the above error shows up.&#160; As you can see from the error, it does not shrink because the file is in use; So you have two options, either change the recovery to simple, shrink the file then change the recovery back http://sequelserver.blogspot.com/2013/07/cannot-shrink-log-file-2-because.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-991235242972277678Wed, 26 Jun 2013 21:49:00 +00002013-06-26T14:49:12.605-07:00&#160; Database owner information is stored in sys.databases view as owner_sid, in order to get the actual mapped name we need to use suser_sname() function 1: select name, suser_sname(owner_sid)as 'owner' from sys.databases<!--CRLF--> 2: select name, suser_sname(sid)'owner' from sysdatabases<!--CRLF--> Change the database Owner Microsoft provides system stored http://sequelserver.blogspot.com/2013/06/script-to-find-database-owners-using-t.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-8957152297508902397Wed, 26 Jun 2013 14:40:00 +00002013-06-26T07:40:58.466-07:00&#160; Download SQL Server 2014 CTP1 and install it in your sandboxed environment – then receive deployment and operations guidance and other resources from the experts. Microsoft SQL Server 2014 brings to market new in-memory capabilities built into the core database, including in-memory OLTP, which complements our existing in-memory data warehousing and BI capabilities for the most http://sequelserver.blogspot.com/2013/06/download-microsoft-sql-server-2014.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-3300755109575389415Fri, 24 May 2013 20:36:00 +00002013-05-24T13:36:33.984-07:00If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB. Members of the server’s Local Administrator’s group can access SQL server by starting it in single-user mode. 1.Enable SQL Single-User Mode - Open SQL Server http://sequelserver.blogspot.com/2013/05/lost-sa-password.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-6288306457998305558Wed, 17 Apr 2013 18:07:00 +00002013-04-17T11:07:40.582-07:00ArchitecturedmvScriptstsqlI have found this query to be a very reliable indicator of CPU pressure in SQL Server 2005 -- Check SQL Server Schedulers to see if they are waiting on CPU SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id &lt; 255 It is much more useful in my opinion than conventional PerfMon indicators like % CPU utilization or http://sequelserver.blogspot.com/2013/04/query-to-find-cpu-pressure.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-3184494314368677777Wed, 17 Apr 2013 18:05:00 +00002013-04-17T11:05:42.330-07:00Architecturedmvselect scheduler_id, &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CAST (cpu_id as varbinary) AS scheduler_affinity_mask from&#160; sys.dm_os_schedulers http://sequelserver.blogspot.com/2013/04/query-to-find-affinity-of-my-schedulers.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-1755907678293470523Wed, 17 Apr 2013 17:50:00 +00002013-04-17T10:50:47.705-07:00ArchitectureSPIDsql server 2008tsqlSPID which stands for&#160; Server Process ID&#160; is essentially a session or a connection in SQL Server. Every time when the application connects to SQL Server, a new connection (or SPID) is created; this connection has a defined scope and memory space and cannot interact with other SPIDs.Each SPID can have it’s own connection settings. Connection settings can be defined by the connection http://sequelserver.blogspot.com/2013/04/what-is-spid-in-sql-server.htmlnoreply@blogger.com (Aneesh)tag:blogger.com,1999:blog-10550791.post-4502806886046168443Wed, 10 Apr 2013 21:21:00 +00002013-04-10T14:34:10.577-07:00 I use temp table on most of my stored procedures for storing the intermediate results, but there was one particular stored procedure some one else in my team created where he used a table variable; He was creating the table variable within a loop, populate the data and processed it, assuming that with each iteration, sql server is going to create another object, the query didn’t generate any http://sequelserver.blogspot.com/2013/04/did-drop-table-temp-actually-deletes.htmlnoreply@blogger.com (Aneesh)