SQLGear

When ever you are renaming a database object you want to make sure you have changed all the code referencing that object. Following is the T-SQL to find a phrase of word in all the database object codes: DECLARE @Text AS NVARCHAR(50) SET @Text = 'DBObjectName' /*Set the variable with the DB Object name, Linked Server name, Database name or just a word string to be searched*/ SELECT DISTINCT sysobjects.NAME ,sysobjects.xtype ,SUBSTRING(syscomments.TEXT, CHARINDEX(@Text, syscomments.TEXT) - ( CASE WHEN CHARINDEX(@Text, syscomments.TEXT) It will give you the name of the object referencing it. Type of the object and also extract of the code around the name of the database object for your reference.

If you have just set up the SQL server 2008 cluster server you will find a job namely SYSPOLICY_PURGE_HISTORY already created under the jobs folder. By default it would be scheduled for daily run. The very next day if you see the job history you will find that this job has failed. If so then  check the PowerShell script inside the job if it refers to the node name as below: (Get-Item SQLSERVER:\SQLPolicy\NodeName\DEFAULT).EraseSystemHealthPhantomRecords() Make this check as a part of you SQL Server Cluster setup checklist. To resolve the failure modify the script by replacing the NodeName with VirtualClusterName  as below: (Get-Item SQLSERVER:\SQLPolicy\VirtualClusterName\DEFAULT).EraseSystemHealthPhantomRecords() This will resolve the issue.

I frequently get request to provide RDLs to developer teams. They do have repository for most of the servers but for those servers which do not have any repository and you need to download RDLs from certain folder it get tedious if there are numerous reports under a folder and its sub folder. The below query will download reports under each folder and subfolder and create same folder structure in the mentioned download folder on the ReportServer. Note: xp_cmdshell should be enabled on the server. --//-- www.SQLGear.net --//-- DECLARE @FullDirectoryPathStatement AS VARCHAR(500) = 'C:\DownloadReport\' SET @FullDirectoryPathStatement = LTRIM(RTRIM(@FullDirectoryPathStatement)) IF RIGHT(@FullDirectoryPathStatement, 1) = '\' SET @FullDirectoryPathStatement = SUBSTRING(@FullDirectoryPathStatement, 1, LEN(@FullDirectoryPathStatement) - 1) DECLARE @FilterReportPath AS VARCHAR(500) = NULL DECLARE @SSRSFolderPath NVARCHAR(850); DECLARE @SSRSFolder NVAR...

Recently trying to restore a database following message was thrown: System.Data.SqlClient.SqlError: File 'F:\DATA\DatabaseName.mdf' is claimed by 'DBFileGroup1'(3) and 'DBFile'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo) The issue might be because of the restore process using same physical file name and path for two different database files. Go to the options tab and see. Following is the screenshot: In any file system there would be no physical files with same name in any given folder. This is why the error comes. Just modify the file name as per your requirement as below: Now hit the OK button. This will restore the database without any error.

Credits unicellular If you are taking backup of a database through wizard or through t-sql script you can get the progress or percent of process completed. This helps when you wait for a backup job to finish before doing your changes to the database. Mostly you plan your changes in prod just after the backup job finishes, and this is obvious option if your database is too big. But the backup job for a database executed by a schedule does not show the progress of it. At this moment you wished how much more you have to wait. Well sys.dm_exec_requests is the DMV for that if you know the session id of the job. SELECT percent_complete FROM sys.dm_exec_requests WHERE session_id = 64 Following script will give you the minutes remaining for the process to be completed. SELECT percent_complete,DateDiff(mi, start_time, GETDATE()) As MinutesTillNow, ((100-percent_complete)*(DateDiff(mi, start_time, GETDATE())))/percent_complete As [MinutesRemaining] FROM sys.dm_exec_requests WHERE session...

Recently one of the developers sent me following error: Message: SQL Error [Microsoft OLE DB Provider for SQL Server: There is insufficient memory available in the buffer pool. SQL State: 42000    Native Error: 802 State: 20     Severity: 17 SQL Server Message: There is insufficient memory available in the buffer pool. I checked the memory usage by components under Memory Consumption server report. Following is the screenshot of the report: I found that CACHESTORE_SQLCP was allocated most memory. This type of memory clerk indicates memory consumption by plans of SQL statements or batches not found in any stored procedures, triggers or functions. I just wanted to clear pool for CACHESTORE_SQLCP. The name for this memory clerk is "SQL Plans". Which I found by querying sys.dm_os_memory_clerks dynamic management view. I then executed the following command to clear the pool for CACHESTORE_SQLCP. DBCC FREESYSTEMCACHE('SQL Plans') The memory a...

Sometimes you may need to find number of rows in each (or few) tables. Following query will determine the number of rows in each partition of each table in each schema.