How to remove Column Header and White spaces in output file

I come across a requirement that I need to create a job on a SQLServer database and generate output separated by a pipe ("|" ) symbol for each column. Also remove all the white spaces between the columns.

As the requirement is to load the data into an ERP system. I found an easy solution using "sqlcmd"

use -W option to remove white spaces

use -h-1 option to remove column heading or header.

Here is the syntax:-

c:\> sqlcmd -W -h-1 -S <Server_name>,<port> -U <user>-P <password> test -i "c:\query.sql" -o "c:\output.txt"

Where
       query.sql - where you will be writing the query
        for ex:- select  @@version
        I would recommend to use "set nocount on" in the query.sql to suppress the record count.

       output.txt  - where you will see the clean output

Hope this helps.

Regards,
Satishbabu Gunukula

How to add Date and Time to output file for Jobs

In one of my project I  come across this requirement that setup a job to run every hour and the output must have a Date and Time.

I found couple of solutions

1. When using SQLServer Management Studio(SSMS) you can add below syntax for output file

SSMS--> SQL Server Agent --> Job Name--> Properties --> Steps --> Select "edit" -->  Advanced 

$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME))

For exL:-
D:\SQLBackup\ouput_file$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt

2.  Use Tokens in Job setup

Refer below MSDN doc
http://msdn.microsoft.com/en-us/library/ms175575.aspx

3. use %date% format with sqlcmd

for ex:-
sqlcmd -S sqlserver,port -U <user> -P <password>  -i "c:\query.sql" -o "c:\ouput_%date%.txt"

You can play around %date% format if you are looking for specific date format.

Regards
Satishbabu Gunukula

How to find the Data and Log File location of all the Databases

I received a SCOM space alert for one of my SQL Server 2008 instance and found that that one of my Data Drive is almost full.

D: disk is at or near capacity. You may need to delete some files

I know that databases are not growing that fast. I released some space by clear the log files, but it didn’t help much.

When I looked into the data and log file folders I see log of files with very old date. It looks like somebody got deleted the database but never cleared the files.

In this situation make sure you first get a list of data and log files with location using below SQL Query then proceed with cleaning the old files.

SELECT Name,Physical_Name AS File_Location
FROM sys.master_files

To find disk space allocate and fee space in SQLServer use below command.

SELECT Name, Filename,

CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],

CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],

CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

FROM dbo.sysfiles a (NOLOCK)

The above SQL provided Name of the Data and log file, File name with Location, Currently Allocated space, Space Used and Available space in SQL Server.

Regards

Satishbaub Gunukula

http://www.sqlserver-expert.com

Error Code: 0x80071398 in SQLServer

Recently we are performed the storage migration of SQLServer 2008. After migration when we are trying to failover the instances we received below error for one the instance.

Error Code: 0x80071398" The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group.

If you read the error it is easy to identify the issue. The node that you are trying to failover may not be the possible owner of that instance.

Possible solution: To fix the issue go to Failover Cluster Manager à Select the instance à go to properties à you should select the NODE in the preferred owner’s List

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

There is insufficient system memory in resource pool 'default' to run this query

We have encounter “insufficient memory” issue on SQLServer cluster and found below error in Event Viewer and Error.log

2014-04-04 05:36:16.83 spid136 Error: 701, Severity: 17, State: 123.
2014-04-04 05:36:16.83 spid136 There is insufficient system memory in resource pool 'default' to run this query. 

Below are the main reasons for the issue

1. The physical memory is completely used and not available for SQLServer
2. SQLServer engine Max memory allocation has been reached limit.
3. Virtual memory is full

First find out which processes are consuming memory, if any tools or application processes outside of sqlserver consuming and then you can close or kill the process, if not important.

Run below command to find out memory Status

DBCC MEMORYSTATUS

You can also run below commands to clear the memory

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Possible workaround/solution:

If the physical memory on the server is full and you have allocated “Maximum Server memory” for SQL Server close the physical memory of the server then I would recommend decreasing the “Maximum Server memory” of SQL Server to left some memory for Operating system.

If your SQLServer engine max server memory is reached the limit and you still have physical memory available then I would advise increasing the “Maximum Server memory” will help.

If your Virtual memory is full then you need more Physical memory and I would advise to add the same.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

Error:18456, Severity:14, State:58 in SQLServer

Users may encounter SQL Server login failures with below error message.

Cannot connect to “SQL Server\Instance”
Additional Information:
Login failed for user “TestUser”.(Microsoft SQL Server, Error: 18456) 

User is able to connect using Windows credentials, but not able to connect using “SQL Server Authentication” using SQL Server Management Studio and ODBC.

Let’s see what is in the ERROR.LOG

2014-01-28 23:06:46.80 Logon     Error: 18456, Severity: 14, State: 58.
2014-01-28 23:06:46.80 Logon      Login failed for user TestDB. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xx.xx.xx.xx]

Cause: The server is configured for Windows authentication only and not able to connect using SQL Server Authentication.

Solution: Modify the Server is to use both SQL Server and Windows Authentication mode.

Right Click on Instance -- Go to properties -- Click on security – change to “SQL Server and Windows Authentication mode” – Restart the Instance

After SQL Server instance restart you should be able to connect. Sometimes users may see below error

provider: SQL Network Interfaces, error: 28- Server doesn’t support requested protocol) (Microsoft SQL Server 

Follow the below link to resolve the issue
http://www.sqlserver-expert.com/2014/01/cannot-connect-to-sql-server-or-instance.html

Thanks,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Cannot Connect to SQL Server or Instance

Installed SQLServer with named instance and when trying to connect from remote host received following error

Cannot connect to “SQL Server\Instance”
Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The Server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28- Server doesn’t support requested protocol) (Microsoft SQL Server)

Error Screen shot: 

There are various reasons for this error, below are the main reasons

1. You might have entered an incorrect connection string – double check that the connection string is correct or not. If you are using the IP address to connect then make sure that you are able to ping the IP.

2. Instances /Server is down , not yet started- Check SQL Server , Agent, SQL Full-Text Filter Daemon Launcher, SQL Server Browser services are up and running. If not running start the services

3. Remote connection on the SQL Server Instance was not enabled

Right Click on SQServer Instance --> Properties -->Go to “Connections”--> “Allow remote connections to this server” should be checked

4. TCP/IP port is not enabled on the Instance

Open Microsoft SQL Server Configuration Manager--> SQL Server Network Configuration -->Protocols for “Instance”--> TCP/IP MUST be “Enabled”. If not ENABLE the same.

You should be able to connect now. If you are using the dynamic port 1433 make sure to change to Static port using below post/URL

http://www.sqlserver-expert.com/2012/07/configure-sqlserver-to-listen-on-static.html

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com 

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2) in SQL Server

Installed SQLServer 2012 Express Edition with Named Instance. When trying to startup the SQL Server Agent to setup the backups, the agent failed with below error

The request failed or the service did not respond in a timely fashion.
Consult the event log or other applicable error logs for details 

When I looked into Event log I found below error message, its not much useful.

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2). 

I looked into the SQLAGENT log and found below error messages

2014-01-28 09:33:07 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1848
2014-01-28 09:33:07 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\SQL-SER1$.
2014-01-28 09:33:09 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2014-01-28 09:33:09 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2014-01-28 09:33:09 - ? [098] SQLServerAgent terminated (normally)

I have changed the SQL Server Agent logon account to administrator and ran below command to enable AgentXPs option and restarted the SQL Server Agent, but still no luck

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0;
GO

When I looked into SQLAGENT log and found out that SQLServer Express edition does not support SQL Server Agent. 

2014-01-28 10:00:31 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1684
2014-01-28 10:00:31 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\Administrators.
2014-01-28 10:00:31 - ? [000]
2014-01-28 10:00:31 - ? [101] SQL Server SQL-SER1\INSTANCE1 version 11.00.2100 (0 connection limit)
2014-01-28 10:00:31 - ? [102] SQL Server ODBC driver version 11.00.2100
2014-01-28 10:00:31 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is SQL-SER1\INSTANCE1
2014-01-28 10:00:31 - ? [310] 4 processor(s) and 8192 MB RAM detected
2014-01-28 10:00:31 - ? [339] Local computer is SQL-SER1running Windows NT 6.2 (9200)
2014-01-28 10:00:31 - ! [000] This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.
2014-01-28 10:00:31 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2014-01-28 10:00:31 - ? [098] SQLServerAgent terminated (normally)

I forgot that I was working on SQLServer Express Edition...:) I hope this post is useful for users, who face  similar situation.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com