List permissions on any object – SQL Server

Tags

object permission, permission, sp_helprotect

Sometimes we need to quickly check the permissions on any database object, below script will help you to extract this information.

Method 1.

USE <DB_Name , Sysname , Database Name>
Go
sp_helprotect '<object name , sysname , object name>'

Note: use Ctrl-Shift-M to replace template parameters.

Though the above method displays the desired result we can not filter the result, to filter the result based on different parameters there is an alternative method as shown below.

Method 2.

USE <DB_Name , Sysname , Database Name>
Go
SELECT
    dp.NAME AS principal_name
    ,dp.type_desc AS principal_type_desc
    ,o.NAME AS object_name
    ,o.type_desc
    ,p.permission_name
    ,p.state_desc AS permission_state_desc
    FROM sys.all_objects o
    INNER JOIN sys.database_permissions p ON o.OBJECT_ID=p.major_id
    LEFT OUTER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
    WHERE o.NAME = '<object name , sysname ,object name>' 
    --AND dp.name = '<principal name , sysname ,login name>'    

Note: use Ctrl-Shift-M to replace template parameters.

Hope this helps…!

SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 have been released

Tags

SQL Server, SQL Server 2008 R2 SP3, SQL Server 2008 SP4, SQL Server Service Pack

Microsoft SQL Server 2008 Service Pack 4 (SP4) and SQL Server 2008 R2 Service Pack 3 (SP3) are now available for download.

Download Service Pack 4 for Microsoft® SQL Server® 2008 

Download Service Pack 3 for Microsoft® SQL Server® 2008 R2

find last day of the month using EOMONTH

Tags

end of the month, EOMONTH, last day of the month

In my previous blog post i have demonstrated how to find first and last date of any month. SQL Server 2012 has introduced a new date function EOMONTH to find the last day of the month.

Syntax
EOMONTH ( start_date [, month_to_add ] )

Arguments
start_date
    Date expression specifying the date for which to return the last day of the month.
month_to_add
    Optional integer expression specifying the number of months to add to start_date.

Examples
EOMONTH without the month_to_add parameter to find last day of current month

EOMONTH with the month_to_add parameter to find last day of last and next month

SQL Server doesn’t provide any function to find start of the month, but we can make use of EOMONTH function to find start date of any month.

EOMONTH function is available in SQL Server 2012 and higher, for SQL Server 2008 R2 and below you can refer to this blog post.

Determining Drive letter database files use

Tags

catalog view, drive letter, sys.master_files

Many a times i need to move database files to new drive letter just to avoid existing drive going out of space, to determine which database files a particular drive host, you can  make use of catalog view sys.master_files as below query.

SELECT  DB_NAME(database_id) [Database Name],  
		Name [Logical File Name] , 
		physical_name [Physical File Name] , 
		Case When type_desc = 'ROWS' Then 'Data' Else 'Log' End [type]  
FROM sys.master_files 
WHERE LEFT(physical_name , 1) = 'C'

You will get result set as below.

Hope this helps…!

Quickly search SQL Server Error Log

Tags

sp_cycle_errorlog, sp_readerro

As a Part of my routine DBA activity, I have to go through sp_readerrorlog to locate errors if any. In our production environment we have have Both Failed and Successful logins auditing enabled. Hence if i run sp_readerrorlog without any filters it would return me huge no of rows and go through each and every row is a time consuming and repetitive activity.
Here i would like to share how we can quickly go through Error Log with few filters.

sp_readerrorlog takes 4 parameters to narrow down our search.

1. @p1 – Log file we want to read, 0 = current error log, 1 = Archive #1, 2 = Archive #2 etc..
2. @p2 – 1 or null = SQL Server error log, 2 = SQL Agent error log.
3. @p3 – string which we want to search.
4. @p3 – string to further refine our search.

I basically filter one day error log as below.

EXEC sp_readerrorlog 0 , null , '2014-04-13 10:00' , '2014-04-14 10:00'

You can also refine your search just to check database backups like below.

EXEC sp_readerrorlog 0 , 1 ,   'Database Mirroring' , 'error'

As sp_readerrorlog is undocumented stored procedure to review error log, you will not find any official references in Books online.

Keep your current error log under control just by recycling it, so that you can quickly retrieve its output, refer sp_cycle_errorlog.

Hope this helps…!

Find SQL Server deatils

Tags

CONNECTIONPROPERTY, IP Address TCP Port Product Name Edition SP, SERVERPROPERTY, SQL Server details

Sometimes DBA’s need to quickly extract few SQL Server details like
SQL Server Name
Instance Name
IP Address
TCP Port
Product Name
Edition
SP Level
Product Version
Collation

Many a times we extract this information by running multiple queries or toggling multiple windows, this T-SQL script will help you to extract same information through a single execution.
You can download T-SQL script from Microsoft gallery.

Basically in this script I have demonstrated how to use SERVERPROPERTY and CONNECTIONPROPERTY functions available in SQL Server 2008 and higher.

Hope this helps…!

SQL Server: Instant File Initialization Exceptions

Tags

IFI, Instant File Initialization, SQL Server, Trace flag 1806

Happy to inform all my blog readers this particular post has won me SILVER in TechNet Guru Award under SQL Server General and Database Engine Technical Guru – February 2014.
Visit TechNet Guru Awards – February 2014 for winner list

Introduction

DBA’s are always on the lookout for Performance Quick Wins to improve the SQL Server performance under their care, one of the highly recommended performance booster is to have Instant File Initialization (IFI) enabled. IFI is a feature that’s seemingly simple;it allows file allocation requests to skip zero initialization on creation as a result, file allocation requests can occur instantly.

I will not get into the details of IFI, we already have good amount of articles on this topic and I personally recommend Instant Initialization – What, Why and How? by Kimberly Tripp and How and Why to Enable Instant File Initialization by Cindy Gross and Denzil Ribeiro.
Basic intention of this article is to demonstrate the Instant File initialization exceptions, which means SQL Server engine will go ahead with zeroing out disk space inspite of having IFI enabled.

Performing the test

Let me check if SQL Server is able to use IFI in my environment by creating a database. If it is enabled you will see message in SQL Server error log for the zeroing out of the log files, and if it is not enabled, you will see messages for the zeroing out for both log files and data files.

I am creating a dummy database with 10 GB data file and 1 GB log file total size would be 11 GB.

Caution

To see which files are being zeroed out, this article uses the undocumented trace flag 3004. Undocumented means it is safe to use in production but any support related to this command cannot be claimed from Microsoft.It also means that any change can be made to this command without officially notifying end user so unexpected results might come if used on same environment after some time. Although their usage is safe, keep in mind to use it in test environments only to avoid any impact to production processes. Trace flag 3004 shows information about backups and file creations, Trace flag 3605 redirects the output to the SQL error log.

USE master;

GO

--Set Trace Flags 3004 and 3605 to On.

DBCC TRACEON(3004,-1);

DBCC TRACEON(3605,-1);

  

--Create a dummy database to see what output is sent to the SQL Server Error Log

CREATE DATABASE IFITest

ON  PRIMARY

(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 10GB)

 LOG ON

(NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf',SIZE = 1GB)

It took 4 sec to create above database on my laptop, let me check SQL errorlog.

2014-02-26 19:12:36.090 spid56       Zeroing c:\IFITest_log.ldf from page 0 to 131072 (0x0 to 0x40000000)
2014-02-26 19:12:40.190 spid56       Zeroing completed on c:\IFITest_log.ldf
2014-02-26 19:12:40.300 spid56       Starting up database 'IFITest'.
2014-02-26 19:12:40.320 spid56       FixupLogTail(progress) zeroing c:\IFITest_log.ldf from 0x5000 to 0x6000.
2014-02-26 19:12:40.320 spid56       Zeroing c:\IFITest_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2014-02-26 19:12:40.330 spid56       Zeroing completed on c:\IFITest_log.ldf

As you can see in above errorlog result, only log file is zeroing out it confirms IFI is enabled in my environment.

Just to cross verify, I create above database by interchanging data file size and log file size but total size of the database remains the same i.e. 11 GB.

Use Master

Go

DROP DATABASE IFITest;

Go

CREATE DATABASE IFITest ON 
PRIMARY

(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 1GB)

 LOG ON

(NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf',SIZE = 10GB)

As expected it took me 1 min 35 sec to create database, this also confirms IFI is in action in my environment.

Note
If you want to check if Instant File Initialization is enabled in your environment then I would recommend you to read http://www.johnsansom.com/sqlserver-instant-file-initialization.

Instant File Initialization Exception Scenarios

Now I demonstrate you few different scenarios wherein IFI has to zero out data files in spite of having IFI enabled.

Scenario 1: Enable Trace Flag 1806.

Turning on trace flag 1806 will disable instant file initialization.

DROP DATABASE IFITest;

GO

DBCC TRACEON(1806 , -1)

go

CREATE DATABASE IFITest ON 
PRIMARY

(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf',SIZE = 10GB)

 LOG ON

(NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf',SIZE = 1GB)

DBCC TRACEOFF(1806 , -1)

Let me check errorlog.

2014-02-26 19:05:41.440 spid56       DBCC TRACEON 1806, server process ID (SPID) 56. This is an informational message only; no user action is required.
2014-02-26 19:05:49.240 spid56       Zeroing C:\IFITest.mdf from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:06:32.070 spid56       Zeroing completed on C:\IFITest.mdf
2014-02-26 19:06:32.160 spid56       Zeroing c:\IFITest_log.ldf from page 0 to 131072 (0x0 to 0x40000000)
2014-02-26 19:06:37.190 spid56       Zeroing completed on c:\IFITest_log.ldf
2014-02-26 19:06:37.320 spid56       Starting up database 'IFITest'.
2014-02-26 19:06:37.330 spid56       FixupLogTail(progress) zeroing c:\IFITest_log.ldf from 0x5000 to 0x6000.
2014-02-26 19:06:37.330 spid56       Zeroing c:\IFITest_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2014-02-26 19:06:37.340 spid56       Zeroing completed on c:\IFITest_log.ldf

As you can see in above result set, enabling trace flag 1806 forces SQL Server engine to zero out both data and log files and it took 48 sec to create database.

Scenario 2: Transparent Data Encryption (TDE).

When you have enable Transparent Data Encryption on your database then SQL Server Engine will ignore your IFI settings and go ahead with zero initialization of data files.

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';

go

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Test Certificate';

go

USE IFITest;

GO

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE IFITest SET ENCRYPTION ON;

ALTER DATABASE IFITest

MODIFY FILE(NAME = 'IFITest' , SIZE = 15GB)

In my above query I encrypted the database and then I altered data file from 10GB to 15GB, let us see errorlog result.

2014-02-26 19:16:28.770 spid56       Setting database option ENCRYPTION to ON for database IFITest.
2014-02-26 19:16:28.770 spid18s      Beginning database encryption scan for database 'IFITest'.
2014-02-26 19:16:42.140 spid56       Zeroing C:\IFITest.mdf from page 1310720 to 1966080 (0x280000000 to 0x3c0000000)
2014-02-26 19:17:06.050 spid56       Zeroing completed on C:\IFITest.mdf

As you can see in result set, SQL Server Engine is zeroed out data file and it took 24 sec to extend data file and without encryption same query took only 1 sec.

Note
Refer technet article Transparent Data Encryption (TDE) to know more about Transparent Data Encryption.

Scenario 3: CHECKDB.

One more case wherein SQL Server Engine has to zero initialize data files is when we run DBCC CHECKDB.

Use IFITest

Go

DBCC CHECKDB

Below errorlog result clearly shows SQL Server Engine is zeroing out data files.

2014-02-26 19:25:08.360 spid53       Zeroing C:\IFITest.mdf:MSSQL_DBCC9 from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:25:08.360 spid53       Zeroing completed on C:\IFITest.mdf:MSSQL_DBCC9
2014-02-26 19:25:08.650 spid53       DBCC CHECKDB (IFITest) executed by \********* found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000013:00000040:0001 and first LSN = 00000013:0000003f:0001.

Note
Refer technet article DBCC CHECKDB to know more about DBCC CHECKDB.

Scenario 4: Database Snapshot.

When we create database snapshot, SQL Server Engine go ahead and zero initializes data file.

CREATE Database IFITest_SS

ON (Name= 'IFITest' , Filename ='C:\IFITest_data.ss')

AS SNAPSHOT OF [IFITest]

And errorlog result indicates zero initialization of data files.

2014-02-26 19:27:46.890 spid53       Zeroing C:\IFITest_data.ss from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:27:46.890 spid53       Zeroing completed on C:\IFITest_data.ss
2014-02-26 19:27:46.940 spid53       Starting up database 'IFITest_SS'.

Note
Refer technet article Create a Database Snapshot to know more about Database Snapshot.

---

Conclusion

Basically, with Instant File Initialization turned on it cuts out database creation or data file growth time, most DBA’s are aware of this fact, but most of us not aware of the fact that in some exceptional cases Instant File Initialization has to zero initializes data files, I have demonstrated four such scenarios in this article.

References

• How and Why to Enable Instant File Initialization by Cindy Gross and Denzil Ribeiro
• Instant Initialization – What, Why and How? by Kimberly Tripp
• SQL Server Instant File Initialization (PQW) by John Sansom
• Do you have Instant File Initialization? by Tibor Karaszi
• Misconceptions around instant file initialization by Paul Randal
• http://technet.microsoft.com/en-us/library/bb934049.aspx

SQL Server: Misleading Database Initial Size Label

Tags

Database Size, DBCC SHRINKFILE, initial size

Happy to inform all my blog readers this particular post has won me GOLD in TechNet Guru Award under SQL Server General and Database Engine Technical Guru – January 2014.
Visit TechNet Guru Awards – January 2014 for winner list

Introduction

In SQL Server every database has a property Initial Size (MB) which can be seen in database properties. It’s the size that is specified during creation of the database. You can either explicitly specify this in the CREATE DATABASE statement,
or you can have SQL Server implicitly copy it from the model database.Please refer

CREATE DATABASE

Well, we all know this, but the question is where exactly this information is stored, can I change Initial size property post its creation, can we shrink a database below its initial size.

In my view, the “Initial size” label that you see when you look at the file properties in SSMS is itself misleading, let us examine it.

---

Performing the test

Create the database as below.

USE master

GO

CREATE
DATABASE MyDB

ON

( NAME = MyDB_dat,

FILENAME = 'c:\MyDB.mdf',

SIZE = 10MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB )

LOG ON

( NAME = MyDB_log,

FILENAME = 'c:\MyDB.ldf',

SIZE = 3MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )

Now check the initial size of mydb (Right click on mydb ->Properties – > files)

Data file has initial size of 10 mb and log file 3 mb as specified in our CREATE DATABASE statement, now alter the database file as below.

ALTER DATABASE
mydb MODIFY FILE (
NAME = N'MyDB_dat',
SIZE = 100MB )

Again check the initial size of mydb(Right click on mydb ->Properties – > files)

Data file initial file size is changed to 100 MB which is current size not initial size, from the above result, it is clear that there is no such thing as “initial size” there is only one property visible and that is the actual size(current
size).

Even the “Initial size” property in SSMS just shows actual size, not the initial size.

Now let me shrink the database and examine to what size it will shrink.

DBCC SHRINKDATABASE('MyDB')

From above result we can clearly make out database has shrinked to the size specified during its creation which is 10 MB.

---

How does database know its initial size?

Well how come that DBCC SHRINKDATABASE knows the initial size then? Even after you have changed the size.

The first page of a database file is the file header page,and it stores information of various properties.

Note
By default, the output of DBCC PAGE is sent to the errorlog. If you want the output to come back to your current connection, need to turn on trace flag 3604, DBCC TRACEON(3604), refer how to use DBCC Page.

DBCC Page would return various properties, we need to concentrate on Size, MaxSize & MinSize, at the creation of database all three properties are set as per the sizes you specified, in our case Size is 10 mb, MaxSize is 100 Mb and MinSize is also 10 MB
which we had specified during database creation.

In the above result all three sizes are in terms of data pages, Size & MinSize is 1280 pages corresponds to 10 MB and MaxSize is 12800 which is 100 MB.

Calculation:
1280 = (1280*8)/1024 = 10 MB
1280 – total no of pages. 8 – 1 page is 8 KB
1024 – 1MB is 1024 KB

Let me alter the file size and see what will happen to these three values.

ALTER DATABASE
mydb MODIFY FILE (
NAME = N'MyDB_dat',
SIZE = 100MB )as you can see that size property has changed to reflect the new size which is 12800 data pages (100 mb). However MinSize still holds the initial size and it is the minimum size to which DBCC SHRINKDATABASE can go to.

---

Can I shrink database below Initial size?

The question is can i change this MinSize. DBCC SHRINKDATABASE Command will shrink database to a size which is specified during database creation which is MinSize, but DBCC SHRINKFILE reduces the size of a file to smaller than its originally created size. The
minimum file size for the file is then reset to the newly specified size. Refer
DBCC shrinkfile

Let us check that.

DBCC SHRINKFILE('MyDB_dat'
, 2)i try and shrink data file to 2 mb and DBCC PAGE MinSize should reflect this new MinSize.

MinSize has new value now, and henceforth this value will be the reference point for Database shrink, let me modify data file to 100 mb and then shrink database and will see to what size database will shrink.

ALTER
DATABASE
mydb MODIFY
FILE ( NAME
= N'MyDB_dat',
SIZE
= 100MB )

GO

DBCC SHRINKDATABASE('mydb')

DBCC SHRINKDATABASE has shrinked data files to 2 mb, that means we have successfully changed MinSize property to new value.

Caution
If you are shrinking files outside of an emergency event, you need to change the way you’re doing things. Refer below articles. http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/

---

Conclusion

Basically, database initial size is just a concept, from a DBA perspective there is no such thing as “initial size” there is only one property visible for a DBA and that is the current size or actual size.

Minimum size is stored in Page header and DBCC SHRINKDATABASE refers to that minsize property when its shrink database if actual data size is less than MinSize.

DBCC SHRINKFILE can shrink files to less than its initial size.

---

References

• http://technet.microsoft.com/en-us/library/ms176061(v=sql.105).aspx
  
• http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx
• http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx
• 
  

Rebuild or Reorganize: Smart SQL Server Index Maintenance

Tags

Alter Index, fragmentation, index fragmentation, index maintenance, Index Rebuild, Index Reorganize, indexes

To keep the indexes in proper shape, a timely defragmentation is necessary. There is no any thumb rule when to defragment the indexes, it depends on the environment. However, this script could be scheduled as per ones need to make sure that the indexes gets defragmented when need.

Firstly do not blindly REBUILD every index which not only increases your I/O’s but also create huge amount of T-log, and if you have enabled mirroring or log shipping then it would take considerable time to ship these logs, defragment your indexes smartly.

In our environment we follow the index maintenance as given below:

If fragmentation is less than 10% or index page count is less than 100 no action will be taken.

If fragmentation is between 10-30% then the index will be reorganized and statistics will be updated. (REORGANISE index will not update STATISITCS automatically)

If fragmentation is greater than 30% index will be rebuilt.

You can also download this script from Microsoft Gallery

Hope this helps.!

Log shipping false errors messages – 14420

Tags

@@ServerName, error 14420, log shipping, log shipping status, SERVERPROPERTY, sp_addserver, sp_dropserver

Problem:
We recently migrated our SQL Server 2008 R2 and SAN storage to the new infrastructure, I have log shipping setup on few databases and to retain all the jobs I restored MSDB database on the new server, post migration I synced primary and secondary servers, enabled all log shipping related jobs and the entire process was successful, but few hours later Primary Server started generating alerts, error 14420 “The log shipping primary database [ServerName].[DatabaseName] has backup threshold of 60 minutes and has not performed a backup log operation for 120 minutes.”

Investigation:
I started my investigation with sp_readeerorlog, I found a lot of error messages as below.

To confirm my log shipping status I checked Log backup , log copy and log restore jobs and all were running successfully, my last log backup was 15 min back and it was successfully restored on secondary server as shown below.

On Primary Server.

Secondary Server.

Now I am pretty sure there is no problem with log shipping, but why server generating alerts, on further investigation using following query on Primary server.

Select * from msdb.dbo.log_shipping_monitor_primary

last_backup_date column was not at all updating with latest dates and it was showing date and time of last backup just before we migrated to new server.
This helped me a lot for my further investigation, and somewhere I read a suggestion just to check my server name.

Select ServerProperty('ServerName'), @@ServerName

Above query returned two different names,ServerProperty(‘ServerName’) returned actual server, whereas @@ServerName returned the name we had assigned to the server during SQL Installation(SQL Server Setup sets the server name to the computer name during installation )

Solution:
When you run following queries all should return you same server name.

Select ServerProperty('ServerName')
Select @@ServerName
Select primary_server from msdb.dbo.log_shipping_monitor_primary

This happens if you install SQL Server and later you change Server name, to solve my problem I have to drop old server name and add it again with new server name as shown in the following queries

EXEC sp_dropserver 'OLD_SERVER_NAME'
EXEC sp_addserver 'NEW_SERVER_NAME', 'local'

You need to restart your SQL Service for changes to take effect, no more error messages now.