Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SqlServerZest.com - SQL in simple style

Suresh Raavi is a database professional, with a zest for SQL Server specializing in Administration and performance tuning. Presently, he is a Sr. SQL Server DBA at the Microsoft Products and Services IT SQL team, taking part in various challenging projects, focusing mainly on performance tuning, database development, high availability and database design. He is passionate about database technologies and "data" in general. He has earned the MCITP Database Administrator 2008, and holds a Master of Science degree in Engineering. He provides insights from the field on his blog at http://SqlServerZest.com. His hobbies includes Running and Travelling. You can follow him on Twitter @SqlServerZest

SQL Server – How to Insert Explicit Values Into An Identity Column

By default, SQL Server automatically assigns a value to the Identity Column for each new row inserted into the table. However, if desired, we can insert explicit values into the Identity column when IDENTITY_INSERT property is set to ON. Let us take an example from AdventureWorks2012 database. Table [HumanResources].[Department] is…

Read more

0 comments, 140 reads

Posted in SqlServerZest.com - SQL in simple style on 2 June 2013

SQL Server – Get the Default Data File and Log File Location using T-SQL Query

In SQL Serve 2012, we can get the default data file and log file locations using a simple T-SQL query as below: SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') AS [Default_Data_path] ,SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') AS [Default_log_path] GO Sample Output: These parameters INSTANCEDEFAULTDATAPATH & INSTANCEDEFAULTLOGPATH are new in SQL Server 2012 and are not documented yet. Usually undocumented…

Read more

0 comments, 228 reads

Posted in SqlServerZest.com - SQL in simple style on 23 May 2013

SQL Server – Does the SQL Agent Service Account Must be a SysAdmin?

As of today (5/14/2013) this MSDN article says “SQL Server Agent service startup account must be a member of the SQL Server sysadmin fixed server role” However, in one of our environments (SQL 2012) I have seen, just a public fixed server role is good enough to start the SQL…

Read more

0 comments, 229 reads

Posted in SqlServerZest.com - SQL in simple style on 14 May 2013

SQL Server – How to Best Remove Extra TempDB Data Files and Log Files

One of the Microsoft’s recommendation for optimizing the tempDB performance is to make each tempdb data file the same size. Today, on one of our servers, I noticed there are 13 data files with different sizes as shown in the below screenshot: My target here is to configure tempdb with…

Read more

8 comments, 181 reads

Posted in SqlServerZest.com - SQL in simple style on 6 May 2013

SQL Server – How to Search/Query the SQL Server Error Log

Often times I keep checking the error log to make sure if everything is running fine. Here is an undocumented stored procedure that really helps simplify our task of searching the error log. Exec XP_ReadErrorLog GO This proc has 4 parameters: Parameter 1 (int), is the number of the log…

Read more

0 comments, 100 reads

Posted in SqlServerZest.com - SQL in simple style on 25 March 2013

SQL Server – Database Engine Error Severities in Brief

When an error is raised by the SQL Server Database Engine, we usually see the Severity Level as in the below example: Msg 5184, Level 16, State 2, Line 1 The severity level of the error indicates the type of problem encountered by SQL Server. Hence looking at the Level…

Read more

0 comments, 85 reads

Posted in SqlServerZest.com - SQL in simple style on 2 November 2012

SQL Server – Transaction Log File is Full; How to Shrink the Transaction Log

Here is a typical scenario every SQL Server DBA faces:  Transaction Log grows unexpectedly or becomes full!! So, do I need to increase the Log size? Fortunately we have a better solution – SHRINK the log file! Typically log truncation frees space in the log file for reuse by the…

Read more

0 comments, 122 reads

Posted in SqlServerZest.com - SQL in simple style on 12 October 2012

SQL Server – Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

When tried to empty one of the tempdb data file using the below T-SQL command: (My goal here is to REMOVE the file using ALTER DATABASE command followed by. For more information on removing data/log files, please refer to this post How to Best Remove Extra TempDB Data Files and…

Read more

0 comments, 224 reads

Posted in SqlServerZest.com - SQL in simple style on 7 October 2012

SQL Server – Understanding ‘Agent XPs’ Server Configuration Option

Today we got an escalation where on one of the servers ‘Agent XPs’ was in a disabled state. And when looked at SQL Server Agent node, it is displayed in Red which basically indicates SQL Agent Service is not running, however when checked the Agent Services are actually running, which…

Read more

0 comments, 140 reads

Posted in SqlServerZest.com - SQL in simple style on 19 September 2012

Newer posts