Archive

Archive for the ‘SQL Tips’ Category

Identify potential free space in Database files before shrinking – TSQL Query

April 27, 2014 3 comments

Today someone in office asked me:

"How can I know how much free space might be left in a Database to Shrink 
before actually Shrinking it?"

 

–> I told it is simple, go to SSMS, select Database -> Right click -> select Task -> select Shrink -> select either Database/File.

DatabaseFreeSpace01
 

–> It will show you Available Free Space, of the whole Database or each mdf/ldf/ndf file(s).

DatabaseFreeSpace02
 

–> He said he already knows it, his real ask was:

"what if you've hundreds of Databases and want to know the stats for each 
of them? Is there any SQL Query which can give you this stats, so that you can 
run it against each or all Database?"

 

I opened SQL Profiler, ran it against the instance and again performed all manual steps defined above to check the Free space. After the Shrink File pop-up window appeared I went back to the Profiler and stopped it, which threw me lot of typical SQL Queries generated by SQL Server DB engine. I searched and found the desired query that I was looking for. I slightly tweaked the query according to my needs and here it is:

USE [AdventureWorks2012]
GO

select 
	db_name()			AS [DatabaseName],
	s.name				AS [DB_File_Name],
	s.physical_name		AS [FileName],
	s.size * CONVERT(float,8) AS [TotalSize],
	CAST(CASE s.type WHEN 2 
			THEN s.size * CONVERT(float,8) 
			ELSE dfs.allocated_extent_page_count*convert(float,8) 
		END AS float)	AS [UsedSpace],
	CASE s.type WHEN 2 
		THEN 0 
		ELSE s.size * CONVERT(float,8) - dfs.allocated_extent_page_count*convert(float,8) 
	END					AS [AvailableFreeSpace] 
from sys.database_files AS s 
left outer join sys.dm_db_file_space_usage as dfs 
	ON dfs.database_id = db_id() 
	AND dfs.file_id = s.file_id
where (s.drop_lsn IS NULL)

–> Output:

DatabaseFreeSpace03
 

To Shrink Database Files check my earlier blog post: https://sqlwithmanoj.wordpress.com/2011/07/02/shrink-database-shrink-file/

Categories: SQL Tips Tags: , , ,

Query to check AlwaysON Replica Latency for the LSNs (Log Sequence Numbers) by using DMVs

April 11, 2014 Leave a comment 

SELECT 
	 ag.NAME AS 'AG Name'
	,ar.replica_server_name AS 'AG Replica Server'
	,DB_NAME(drs.database_id) AS 'Database Name'
	,CASE 
		WHEN ars.is_local = 1
			THEN N'LOCAL'
		ELSE 'REMOTE'
	END AS 'Is AG Replica Local'
	,CASE 
		WHEN ars.role_desc IS NULL
			THEN N'DISCONNECTED'
		ELSE ars.role_desc
	END AS 'AG Replica Role'
	,ar.availability_mode_desc AS 'Sync Mode'
	,drs.synchronization_state_desc AS SyncState
	,agl.dns_name AS 'Listener Name'
	,drs.last_hardened_lsn
	,drs.last_hardened_time
	,datediff(s, last_hardened_time, getdate()) / 60 AS 'Seconds Behind Primary'
	,drs.last_commit_time

FROM sys.availability_groups AS ag 

INNER JOIN sys.availability_replicas AS ar 
ON ag.group_id = ar.group_id

INNER JOIN sys.dm_hadr_availability_replica_states AS ars 
ON ar.replica_id = ars.replica_id

INNER JOIN sys.dm_hadr_database_replica_states drs 
ON ag.group_id = drs.group_id
AND drs.replica_id = ars.replica_id

INNER JOIN sys.availability_group_listeners AS agl
ON agl.group_id = ars.group_id

ORDER BY datediff(s, last_hardened_time, getdate()) DESC;
Categories: SQL Tips Tags: ,

SQL Tips – Search and list out SQL Jobs containing specific word, text or SQL Query

December 12, 2013 Leave a comment

 
Today while working on one database migration project I wanted to check a particular Stored Procedure is called by what all SQL Jobs.

So, I created following queries to pass the SP name in the where clause and get respective SQL Job name.

The 1st query just gives the SQL Job name, and the 2nd query gives the SQL Job name with Job Step name, SQL Command used in that step, and target Database.

use msdb
go

-- #1. List out all SQL Jobs:

select distinct j.name
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
where js.command like '%spStartSystem%'
order by j.name


-- #2. List out all SQL Jobs with SQL command, Step name and target Database:

select 
	j.name, 
	js.step_name, 
	js.command, 
	js.database_name
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
where js.command like '%spStartSystem%'
order by j.name
Categories: SQL Tips Tags: , ,

Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012

September 20, 2013 6 comments

I was working on a legacy T-SQL script written initially on SQL Server 2005 and I was facing an unexpected behavior. The code was giving me unexpected records, I tried to dig into it and found that ISNUMERIC() function applied to a column was giving me extra records with value like “,” (comma) & “.” (period).

–> So, to validate it I executed following code and found that ISNUMERIC() function also passes these characters as numbers:

SELECT 
	 ISNUMERIC('123') as '123'
	,ISNUMERIC('.') as '.' --Period
	,ISNUMERIC(',') as ',' --Comma

Function ISNUMERIC() returns “1” when the input expression evaluates to a valid numeric data type; otherwise it returns “0”. But the above query will return value “1” for all 3 column values, validating them as numeric values, but that’s not correct for last 2 columns.

–> And not only this, ISNUMERIC() function treats few more characters as numeric, like: – (minus), + (plus), $ (dollar), \ (back slash), check this:

SELECT 
	 ISNUMERIC('123') as '123'
	,ISNUMERIC('abc') as 'abc'
	,ISNUMERIC('-') as '-'
	,ISNUMERIC('+') as '+'
	,ISNUMERIC('$') as '$'
	,ISNUMERIC('.') as '.'
	,ISNUMERIC(',') as ','
	,ISNUMERIC('\') as '\'

This will return “0” for second column containing value “abc”, and value “1” for rest of the column values.

So, you will need to be very careful while using ISNUMERIC() function and have to consider all these possible validations on your T-SQL logic.

– OR –

Switch to new TRY_PARSE() function introduced in SQL Server 2012.

–> The TRY_PARSE() function returns the result of an expression, translated to the requested Data-Type, or NULL if the Cast fails. Let’s check how TRY_PARSE() validates above character values as numeric:

SELECT 
	 TRY_PARSE('123' as int) as '123'
	,TRY_PARSE('abc' as int) as 'abc'
	,TRY_PARSE('-' as int) as '-'
	,TRY_PARSE('+' as int) as '+'
	,TRY_PARSE('$' as int) as '$'
	,TRY_PARSE('.' as int) as '.'
	,TRY_PARSE(',' as int) as ','
	,TRY_PARSE('\' as int) as '\'

So, the above query gives me expected results by validating first column value as numeric and rest as invalid and returns NULL for those.

–> TRY_PARSE() can be used with other NUMERIC & DATETIME data-types for validation, like:

SELECT 
	 TRY_PARSE('123' as int) as '123'
	,TRY_PARSE('123.0' as float) as '123.0'
	,TRY_PARSE('123.1' as decimal(4,1)) as '123.1'
	,TRY_PARSE('$123.55' as money) as '$123.55'
	,TRY_PARSE('2013/09/20' as datetime) as '2013/09/20'

… will give expected results 🙂

Categories: SQL Server 2012, SQL Tips Tags: , ,

Script out all SQL Jobs – MSDN TSQL forum

June 6, 2013 Leave a comment

–> Question:

Hi experts,

I have approx 200 jobs in production env.

Can any one give me the script, which will script all SQL Jobs for me. I wont need any manual process please, any code, or shell script which script all SQL Jobs so that I can have some sort of backup for them, MSDB backup is not choice.
 

–> My Answer:

In SSMS, open “Object Explorer Details” by pressing F7.

Go to “SQL Agent” -> Jobs -> Select all jobs, Right click and “Script Job as” -> “Create to”

All, Jobs will be scripted at once.
 

Ref link.

Categories: SQL Tips Tags: ,