SqlServer-query

Limiting the size of sql server error log is one of the features in SQL server 2012 which we could consider using. We could limit the log file size from SQL 2012 which would ensure that disk space is not eaten up by huge sql server error logs. Refer below article and blog http://support.microsoft.com/kb/2199578 http://www.sqlskills.com/blogs/paul/limiting-error-log-file-size-in-sql-server-2012/ ...

While connecting sql server 2012 using sql server 2008 SSMS, you will receive error  Index was outside the bounds of the array. Below article deals with the issue and we would have to be in certain service pack levels to fix the same.  In case you have SQL Server 2008 R2 management studio in your ...

SQL Server will not allow you to drop table without dropping foreign key constraint, foreign key means column of one table refers primary key of another table. This relationship called a Foreign key relationship, here primary table will act as parent table and secondary table called as child table. If you try to drop primary table (Parent ...

As SQL DBA, we often deal with orphan user related issues while moving database from one server to another. We got an email from client saying they are unable to connect database server after database movement … Login Eror Message was “Login failed for user ‘idb_dev’.(Microsoft SQL Server, Error: 18456)”.  also Error 15023: User already exists in ...

Grant permission using sql server certificate to execute system stored procedure like sp_updatestats. Sql server certificate is helpful to provide access to different users to execute certain stored procedure which require exclusive privilege after valid authentication method. Example 1. Create a stored procedure to execute sp_updatestats USE [master] GO CREATE PROCEDURE [dbo].[sp_updatestats_user] AS BEGIN exec ...

To keep track on database transaction log growth, you can use DBCC SQLPERF command that monitors transaction log space used in the database. Log file size is depends on recovery model of database and backup job.  Frequently running transaction log backup can keep transaction log in good shape. Sql server has dbcc sqlperf command to track ...

Sp_msforeachtable SQL Server has sp_msforeachtable as undocumented stored procedure that can be used to run query against all table in single database. Lets say you want to get the record count from each table so in that case you can use below command to generate the list of tables as well as record count. Example ...

Sp_msforeachdb can be handy to use as option to perform maintenance task for each database in sql server. sp_msforeachdb is undocumented stored procedure and not supported by Microsoft.  Sp_msforeachdb will help you to iterate through all database and execute your sql command.  Sp_msforeachdb  may be discontinued in future version of sql server. DBA use is ...