SQL First and Last Day Of a Month

In This SQL Tutorial show you retrieve First Day and Last Day for a month using SQL Query.

It can dynamically to retrieve First Day and Last day for a month using SQL Query.

Example SQL Statement or Query below show you how to retrieve First Day or Last Day for a month. To display the date in different format, click here.
DECLARE @Today DATETIME
SELECT @Today = '6/17/2007'

Get First Day of a Month Using SQL Query
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-3,@Today)) 
Value = 2007-03-01 00:00:00.000 

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-2,@Today))Value = 2007-04-01 00:00:00.000
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today))Value = 2007-05-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))Value = 2007-06-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))Value = 2007-07-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,2,@Today))Value = 2007-08-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,3,@Today))Value = 2007-09-01 00:00:00.000


Get Last Day of a Month Using SQL Query
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-2,@Today))
Value = 2007-03-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-1,@Today))
Value = 2007-04-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,0,@Today))
Value = 2007-05-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))
Value = 2007-06-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,2,@Today))
Value = 2007-07-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,3,@Today))
Value = 2007-08-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,4,@Today))
Value = 2007-09-30 00:00:00.000

Database Mirroring - Common Errors

Database Mirroring Errors

The following errors are the commonly occuring in 
database mirroring and hence 
I thought of collecting them and providing solution 
as well so that it might 
benefit others
Error:
Login Failure connecting to new principal server after failover
Error: 18456 Severity: 14 State: 16
The problem is that after performing failover the
 original mirror database becomes 
the new principal. We can connect successfully to 
the mirror using Windows login.
But we are unable to connect to the new principal database with 
Sql login as we get the below,
Cannot open database "" requested by the login. The login failed.


Solution:
This problem occurs because the SIDs (security identifiers) 
for the SQL Server logins
 on each server do not match. Although the names 
for the logins are the same, the 
login is resolved via the SID. This is not a problem 
with Windows/Domain user/group
 logins because the SIDs for these logins are created 
based on the domain SID for
 the user/group, and hence will be the same for the 
same given user/group.


1. You can either run the procedure sp_change_users_login
 to map the user
 and login (or )
2. At the time of creating logins in the mirror server, you
 will need to create 
the SQL Server logins on the mirror server not only with 
the same name, but
 also with the same SID as on the principal server.


Execute the below command in Mirror server to create the 
same login as in 
original principal server with the same SID as in the original 
principal server


Create Login "login name" With Password = "Password" , 
SID = "Sid of the same
 login from original principal server"


You can obtain the SID of the same login from original
 principal server using,


select name,sid from sys.sql_logins where name ='Login name'
Error:When you configure mirroring you might receive
 the below errror,
One or more of the server network addresses lacks a 
fully qualified domain name 
(FQDN). Specify the FQDN for each server, and click start 
mirroring again

Solution:

Assume that your principal server is A and mirror server is
 B and you have configured 
mirroring for Adventure Works database. The fully qualified 
computer name of each
 server can be found running the following from the
 command prompt:

IPCONFIG /ALL

Concatenate the "Host Name" and "Primary DNS Suffix". 
If you see something like:

Host Name . . . . . . . . . . . . : A

Primary Dns Suffix . . . . . . . : corp.mycompany.com

Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':' 
and you then have the partner name.

-- Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER =
 'TCP://A.corp.mycompany.com:5022';

-- Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER =
 'TCP://B.corp.mycompany.com:5022';

Pls replace your database name accordingly in the above command

Error:
Msg 1416, Level 16, State 31, Line 3. Database is not configured 
for database mirroring

Solution: 
You need to restore the Full backup from principal server 
using With NoRecovery 
option and also one transactional log backup from principal 
server using With
 NoRecovery 
option and then start configuring mirroring.

Relinquish the CPU BottleNeck on Win Server 2008 64-bit

CPU Performance Bottleneck

Server Specification

Dell PowerEdge T610

Processor Intel Quad Core Xeon COU, 2.8 GHz

Memory 16GB 4x4GB

Operating System: MS Windows Server 2008 Standard x64 Ed.

Database: MS Sql Server 2008 x64

Summary

Initially the Server T610 was running on single instance IDBSVR\INS_ICARE to manage ICARE database. The hardware setup  on CPU and Memory was based on default setting. We only reconfigure the current RAID setup from RAID 5 to RAID 10. The ICARE database was replicated continously to another Windows 2003 Server I.e DBSVR.

We used Spotlight on SQL Server Enterprise to monitor the database performance and track any significant errors.

Symptom

On April ~ May 2011, we started to install Boss.Net application and create another instance IDBSVR\BOSS_NET_DB. This database also was replicated to DBSVR on scheduled basis.

However, the Spotlight monitoring software started to view alarming indicator which shows bottleneck on CPU performance.

Benchmark

Processor Queue Length (CPUs x 2) = 32 (Logical CPU)

                                                            = 16 (Physical CPU)

            Preliminary Trial Setup = 16

Context Switches/Sec                         = 8000

Message Alarms

The IDBSVR started to broadcast Low level alarm indicating high number of Processor Queue Length which is exceeding 16 for more than 10 minutes and Context Switching level has started to reach 2 million counters.

The symptoms started to appear after 48 hours of restarting the server.

Solution and Action

1.      Set Maximum Worker Threads =  704 in Server Properties under Processors Tab on IDBSVR\INS_ICARE*

2.      Restart the server.

3.      After 48 hours, the problem still happened.

4.      Set Maximum Worker Threads =  704 in Server Properties under Processors Tab on IDBSVR\IBOSS_NET_DB*

5.      Restart the server.

*The default value 704 on Max Worker Thread is taken from MSDN blog.