-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

MSSQLFUN

I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.

Database snapshot issue with sqlserver

Database snapshot issue with sqlserverDatabase snapshot issue with sqlserverDatabase snapshot issue with sqlserverDatabase snapshot issue with sqlserverDatabase snapshot issue with sqlserverDatabase snapshot issue with sqlserver

Database snapshot issue with sqlserverDatabase snapshot issue with sqlserverDatabase snapshot issue with sqlserverDatabase snapshot issue with sqlserver Read more

0 comments, 184 reads

Posted in MSSQLFUN on 23 May 2017

Test Case for Mssql server11

Test Case for Mssql server11Test Case for Mssql server11Test Case for Mssql server11Test Case for Mssql server11 Test Case for Mssql server11Test Case for Mssql server11Test Case for Mssql server11

Read more

0 comments, 208 reads

Posted in MSSQLFUN on 19 May 2017

Test Case for Mssql server

the image i.e screen shot can not be taken for sqlservrer

Read more

0 comments, 209 reads

Posted in MSSQLFUN on 19 May 2017

Database snapshot could not be created and the database or table could not be locked

DBCC CheckDB failed with error “database snapshot could not be created and the database or table could not be locked”

Error: The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of… Read more

0 comments, 361 reads

Posted in MSSQLFUN on 18 May 2017

Cumulative Update #2 for SQL Server 2016 SP1

The 2nd cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

To learn more about the release or servicing model, please visit:

Read more

0 comments, 475 reads

Posted in MSSQLFUN on 22 March 2017

Always on\Database Mirroring Automatic Page Repair

A special thanks to Daniel Jones [daniel.jones.0543@gmail.com] for showing up interest and writing informative article for our blog (http://mssqlfun.com/).

 

Always on\Database Mirroring Automatic Page Repair

One of the most useful as well as hottest feature in SQL Server is mirroring of database. It is helpful for numerous of… Read more

0 comments, 291 reads

Posted in MSSQLFUN on 21 March 2017

Cumulative Update #11 for SQL Server 2014 SP1

The 11th cumulative update release for SQL Server 2014 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

Please visit:

Read more

0 comments, 226 reads

Posted in MSSQLFUN on 13 March 2017

How Row versioning impact tempDB ?

Scenario: DBA found that tempdDBdatabase usage is getting high and most of the size is consumed by row versioning. DBA raised the issue with application team running that query. Once Application team close the session, tempDB usage comes normal.

Question: Application tea raise concern “How can a select statement on… Read more

0 comments, 376 reads

Posted in MSSQLFUN on 6 March 2017

Error while loading Excel data into database

Issue: I am trying to load data from Excel 97 – 2003 sheet into a database table. I have written a simple SSIS package to do that, but unfortunately, it fails with error: –

 

Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation.… Read more

1 comments, 1,874 reads

Posted in MSSQLFUN on 21 February 2017

Restrict user to login from single Host

Restricting user to login from single host may be sometimes required from security point and other business requirements. We can achieve it through SERVER LEVEL LOGON TRIGGER.

ALTER TRIGGER TR_CHECK_LOGIN_TEST_HOST
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @HOSTNAME VARCHAR(48)
DECLARE @PROGNAME VARCHAR(100)

SELECT @HOSTNAME = HOST_NAME FROM SYS.DM_EXEC_SESSIONS Read more

0 comments, 209 reads

Posted in MSSQLFUN on 7 February 2017

Write-Host || Cannot invoke this function because the current host does not implement it

Issue: While running powershell in SQL job step, I am getting below error: –

A job step received an error at line 13 in a PowerShell script. The corresponding line is ‘Write-Host “ServerName: ” $comp’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot… Read more

0 comments, 254 reads

Posted in MSSQLFUN on 1 February 2017

Error While running PowerShell code from SQL Agent job – “Path Does Not Exist”

Issue: When I am running code from PowerShell console its running fine but when I execute it through SQL agent job step, I am getting below error: –

The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line… Read more

1 comments, 468 reads

Posted in MSSQLFUN on 23 January 2017

What’s new in SQL Server 2016 installation?

1) As MS is kept increasing number of option while installation. To reduce no. of click, if setup pass Rules it will automatically moves to Next window.

2) On Server Configuration page, MS specifically ask to GRANT PERFORM VOLUME MAINTENANCE TASK permission to service account. SQL Services needs this access… Read more

0 comments, 319 reads

Posted in MSSQLFUN on 16 January 2017

Sequence – Long awaited feature

As per BOL (https://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx) : A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle… Read more

0 comments, 261 reads

Posted in MSSQLFUN on 5 January 2017

What is “SET STATISTICS TIME”?

When you turn on this option, it will display the number of milliseconds required to parse, compile, and execute each statement. By default, this option is disabled.

SET STATISTICS TIME ON;

GO

SELECT * FROM [HUMANRESOURCES].[EMPLOYEE]

Output: –

SQL Server parse and compile time: 
 CPU time = 0 ms, elapsed…

Read more

0 comments, 242 reads

Posted in MSSQLFUN on 19 December 2016

What is “SET STATISTICS TIME”?

When you turn on this option, it will display the number of milliseconds required to parse, compile, and execute each statement. By default, this option is disabled.

SET STATISTICS TIME ON;

GO

SELECT * FROM [HUMANRESOURCES].[EMPLOYEE]

Output: –

SQL Server parse and compile time: 
 CPU time = 0 ms, elapsed…

Read more

0 comments, 238 reads

Posted in MSSQLFUN on 19 December 2016

What is “SET STATISTICS TIME”?

When you turn on this option, it will display the number of milliseconds required to parse, compile, and execute each statement. By default, this option is disabled.

SET STATISTICS TIME ON;
GO
SELECT * FROM [HUMANRESOURCES].[EMPLOYEE]

Output: -
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed…

Read more

0 comments, 268 reads

Posted in MSSQLFUN on 19 December 2016

How to use DTA (Database Tuning Advisor) with Plan Cache option?

SQL Server 2012 comes up with new workload option in DTA (Database Tuning Advisor). In addition, on existing options File or Table, Microsoft gives option to fetch workload from plan cache of SQL Server. In this case, the DTA will select the top 1,000 events from the plan cache based… Read more

0 comments, 471 reads

Posted in MSSQLFUN on 12 December 2016

MSDB log file grown large – SLEEP_MSDBSTARTUP waittype

Issue: MSDB database log file is getting full very rapidly and became very huge. Currently, T-Log file is of 5GB whereas Data file is only 1GB for MSDB on server.

Findings: We found system owned open transaction in MSDB which is causing issue.

Transaction information for database ‘msdb’.

Oldest active… Read more

0 comments, 365 reads

Posted in MSSQLFUN on 30 November 2016

Older posts