Mentor in need

I guess I can say that , my life is sort of aimless for now.. newer technologies entice me in general green energy, smart cities, IOT ( scares some times thinking of all the ill effects) , Nano Tech ( my absolute fav) , Flying\Space (my dream) and many more.  I have issues expressing myself, like writing here I am just direct and hence run out of thoughts to continue to write hence my blogs are short direct to point all the time like my conversations.  I guess becoming a DBA was by accident, chose something my roomies chose while doing my Master’s, my dream was to be in the Airforce back home in India which didn’t happen. Dad gave me 2 options pick Indian Administrative service or US and here I am. I have issues focusing on learning something in depth and tend to loose track easily and want to learn something else same there and eventually get confused with all the options and  hence nothing is perfected. I am really confused on the path that I need to pick, continue working on technologies ( Azure, ML, RL) though I really hate core programming or manage people and teams and grow. Options at the current place for growth is really really limited as I am working as a contractor ( don’t really want to but thanks to the Immigration services). I also tend to help my wife a lot these days managing home and kids along with work as she is pursuing her Phd.

I always feel I am in need of a good mentorship to decide how I need to plot my course going forward.

Posted in Uncategorized | 1 Comment

Find Legacy SQL Joins

— will list both Views and Stored procedures in a particular database

SELECT OBJECT_NAME(object_id),OBJECT_DEFINITION(object_id) FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%*=%’ or OBJECT_DEFINITION(object_id) LIKE ‘%=*%’

Posted in Administration | Leave a comment

Inplace Upgrade Win 2008 to 2008R2

Just wanted to write this for the fun of it, I know it is an old edition which is out of main stream support as of Jan 2015, it has extended support till 2020 WindowsSupport_LifeCycle

The upgrade is pretty self explanatory. Also please take a backup of the whole server before starting the process. If just databases, back them up and copy them to an external device or a shared drive that’s not on this server so that we can rebuild SQL in case the upgrade corrupts the server.

Start the install process, Select “Install now” Tab

1

Then Select “Go online for Update’s” a recommended one (hopefully the server can connect to the web)
2

3
4

5

 

Click Upgrade
6

7
8

System reboots automatically many times over the period of the upgrade at the above step. If you are on a virtual environment connect to the server thru Vcenter.

9
10
11

12
13

It creates a new NIC along with the Existing one.. Reconfigure the NIC for you to be able to ping or RDP in to the server after the upgrade.

Old accounts work, log back in to the server and verify everything including SQL.

 

Posted in Windows | Leave a comment

MSSQL Replication between different SQL versions configuring Subscriptions

Came across an instance at work, where I had to set up replication between 2 different SQL versions,

Publisher: SQL 2005 SP3
Subscriber: SQL 2008 R2
Type: Push Subscription
Distributor: Publisher

1) Configure Publication on the Publisher
This article doesn’t describe the steps for configuring a Publisher, assuming here the Publications are already set ( will write up another article on a later date for Publication part)

2) Configure Subscriptions on the Subscriber
a)
1

b) Pick the Publisher
b

c)
c

d) Connect to the Publisher using “sa” account or any other SQL account.

d

e) Pick the Publication that needs to be subscribed
e

f) Pick the Distributor, in this case it is same as Publisher
f
g) Subscription database that holds the published table is picked here

g
h) Connecting to the Distributor and Subscriber, click on the ellipsis

h

h1

h2

i) Schedule the subscription to run after the snapshot SQL agent job is completed on the
Publisher.
i
i1
j) We can trigger the subscription immediately or after the snapshot is generated.
j
j1

k) Click finish and the subscription gets created with a job scheduled to run on the
Publisher.
k
k1

l) Repeat the same to set up multiple subscriptions, you can see the list of
subscriptions under “Local Subscriptions” in subscriber as well as under “Local
Publications” in the Publisher.

Posted in Uncategorized | Leave a comment

Actual Database Size

The below script would provide the Actual DB file usage size rather than DB size (Used + Free) should work for SQL 2008 and upwards

Borrowed the “dbo.sp_foreachdb” script from Aaron Bertrand (making-a-more-reliable-and-flexible-spmsforeachdb/) to list out all databases in a SQL server.

Step1: Create the below stored procedure.

USE [master];
GO
CREATE PROCEDURE dbo.sp_foreachdb
@command             NVARCHAR(MAX),
@replace_character   NCHAR(1)       = N’?’,
@print_dbname        BIT            = 0,
@print_command_only  BIT            = 0,
@suppress_quotename  BIT            = 0,
@system_only         BIT            = NULL,
@user_only           BIT            = NULL,
@name_pattern        NVARCHAR(300)  = N’%’,
@database_list       NVARCHAR(MAX)  = NULL,
@recovery_model_desc NVARCHAR(120)  = NULL,
@compatibility_level TINYINT        = NULL,
@state_desc          NVARCHAR(120)  = N’ONLINE’,
@is_read_only        BIT            = 0,
@is_auto_close_on    BIT            = NULL,
@is_auto_shrink_on   BIT            = NULL,
@is_broker_enabled   BIT            = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@sql    NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db     NVARCHAR(300),
@i      INT;

IF @database_list > N”
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) – 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,”,’,’),
”,”),”,”)
FROM
(
SELECT DISTINCT x = ‘N”’ + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(‘,’, @database_list + ‘,’, n) – n))) + ””
FROM n WHERE n <= LEN(@database_list)
AND SUBSTRING(‘,’ + @database_list, n, 1) = ‘,’
FOR XML PATH(”)
) AS y(x);
END

CREATE TABLE #x(db NVARCHAR(300));

SET @sql = N’SELECT name FROM sys.databases WHERE 1=1′
+ CASE WHEN @system_only = 1 THEN
‘ AND database_id IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @user_only = 1 THEN
‘ AND database_id NOT IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @name_pattern <> N’%’ THEN
‘ AND name LIKE N”%’ + REPLACE(@name_pattern, ””, ”””) + ‘%”’
ELSE ” END
+ CASE WHEN @dblist IS NOT NULL THEN
‘ AND name IN (‘ + @dblist + ‘)’
ELSE ” END
+ CASE WHEN @recovery_model_desc IS NOT NULL THEN
‘ AND recovery_model_desc = N”’ + @recovery_model_desc + ””
ELSE ” END
+ CASE WHEN @compatibility_level IS NOT NULL THEN
‘ AND compatibility_level = ‘ + RTRIM(@compatibility_level)
ELSE ” END
+ CASE WHEN @state_desc IS NOT NULL THEN
‘ AND state_desc = N”’ + @state_desc + ””
ELSE ” END
+ CASE WHEN @is_read_only IS NOT NULL THEN
‘ AND is_read_only = ‘ + RTRIM(@is_read_only)
ELSE ” END
+ CASE WHEN @is_auto_close_on IS NOT NULL THEN
‘ AND is_auto_close_on = ‘ + RTRIM(@is_auto_close_on)
ELSE ” END
+ CASE WHEN @is_auto_shrink_on IS NOT NULL THEN
‘ AND is_auto_shrink_on = ‘ + RTRIM(@is_auto_shrink_on)
ELSE ” END
+ CASE WHEN @is_broker_enabled IS NOT NULL THEN
‘ AND is_broker_enabled = ‘ + RTRIM(@is_broker_enabled)
ELSE ” END;

INSERT #x EXEC sp_executesql @sql;

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT CASE WHEN @suppress_quotename = 1 THEN
db
ELSE
QUOTENAME(db)
END
FROM #x ORDER BY db;

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@command, @replace_character, @db);

IF @print_command_only = 1
BEGIN
PRINT ‘/* For ‘ + @db + ‘: */’
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ @sql
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END
ELSE
BEGIN
IF @print_dbname = 1
BEGIN
PRINT ‘/* ‘ + @db + ‘ */’;
END

EXEC sp_executesql @sql;
END

FETCH NEXT FROM c INTO @db;
END

CLOSE c;
DEALLOCATE c;
END

Step2: Run the below on the Server to generate the report, it can be modified as need by adding more columns or removing them.

Create Table ##temp
(
DatabaseName sysname,
DB_File_Name sysname,
[Size-Mb] decimal (18,2),
[Used-Mb] decimal (18,2),
[Maximum_Size-Mb] decimal (18,2),
[Growth] varchar(20),
[GroupName] sysname,
[%Free] int
)
Exec sp_foreachdb’
Use ?;
Insert Into ##temp (DatabaseName, DB_File_Name, [Size-Mb], [Used-Mb],[Maximum_Size-Mb],[Growth],[GroupName],[%Free])
Select DB_NAME() AS [DatabaseName], Name,
cast(size/128.0 as numeric(10,1)) as [size-mb],
cast(FILEPROPERTY(name, ”spaceused”)/128.0 as numeric(10,1)) as [used-mb],
maxsize [maximum size-mb],
cast(growth*8/1024 as varchar(11))+
case when status&0x100000>0 then ”%” else ”” end [growth],
isnull(filegroup_name(groupid),”log”) [groupname],
cast(100*(size/128-(FILEPROPERTY(name,”spaceused”)/128))/(size/128.0) as int) [% free]
from sysfiles

Select * From ##temp
drop table ##temp

Posted in Administration, MSSQL | Leave a comment

Coalesce Inside a Case

Got in to an instance at work where i had to modify a SP to get non-null values out of a case statement code block

CASE
WHEN condition_expression THEN  COALESCE(Column1, Column2)
ELSE Column2
END AS New_Column

Posted in Development | Leave a comment

MySQL Startup error

Installed an windows version of MySQL and was trying to start it from a cmd prompt.

To Start MySQL server
from command line, you should start a console window (or “DOS window”) and enter the below or direct your command to where your MySQL binaries are installed on your system:
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld”

If you get an error as below from running the above

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. please use –explicit_defaults_for_timestamp server option

the issue can be fixed by adding the below to my.ini , under “mysqld” section

# Enable the query cache
explicit_defaults_for_timestamp = true

run the below again to successfully start the services
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld”

To check MySQL server status
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin” -u root -p status

To Stop MySQL server
C:\> “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin” -u root -p shutdown

Posted in Administration, MySQL | Leave a comment

User and User Group Queries

I get user requests all the time for access rights and i don’t have read access to AD to check groups to see if the user is already a member or not.

We can run the below scripts from SQL server Management Studio

Domain Level:
— Collect AD Groups for “Domain Account” and stores it in a Temp table
EXECUTE AS LOGIN = ‘Domain Account’
SELECT name INTO #User from sys.login_token
WHERE TYPE = ‘WINDOWS GROUP’
REVERT

— Query the list of AD Groups that the user account belong to from Temp Table
SELECT name FROM #User

Server Level:
The below just are localized to the server, the queries work just for Domain user or Group that have access to the SQL server.If you include the ones not on the server they dont return any results.

/*This query based on Domain account passed lists all of the groups it belongs to and kind of permissions they have including the Domain account itself listing with in that server*/
EXEC master.dbo.xp_logininfo’Domain User’,’all’

— This query lists all members in a group.
EXEC master.dbo.xp_logininfo’MyDomain\MyGroup’,’members’

References:

http://sqlstudies.com/2014/12/29/finding-a-common-ad-group-for-a-set-of-users/
http://royalsql.com/2013/08/05/the-fairytale-girls-in-deep-distress-and-had-to-query-acitve-directory/

Posted in MSSQL, Permissions | Leave a comment

Consistency Errors due to MSG 8914 – PFS free space

Issue

While running DBCC CHECKDB() job on SQL 2008 R2 SP1 with SQL 2005 compatibility Level databases, it failed with the below DBCC results for ‘MESTEMPLATEDOCUMENTS’.

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:87991) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:88697) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91929) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91931) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91932) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

There are 0 rows in 1 pages for object “MESTEMPLATEDOCUMENTS”.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS'(object ID 1394416337).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (dbo.MESTEMPLATEDOCUMENTS’).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Resolution

After a Brief research found this solution from Paul Randall
http://www.sqlskills.com/blogs/paul/corruption-bug-that-people-are-hitting-msg-8914-pfs-free-space/

As solution didn’t work, I had to go ahead and do the repair

Implementing the repair on Dev or Test first to make sure the repair doesn’t cause any unintended results. Please take a Backup of the Database on which you would be working

Steps Followed:
1) Run DBCC Checktable(tablename) to verify if the issue is still occurring
2) If it does, query the table to see if you have any data in there to gauge the data loss after
the repair.
3) The table in question is empty
4) Change DB to “Single User Mode”
USE [master]
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
5) Run the below to correct the errors
DBCC CHECKTABLE (MESTEMPLATEDOCUMENTS, REPAIR_ALLOW_DATA_LOSS )

Results are as follows

with All_ErrorMsgs DBCC results for ‘MESTEMPLATEDOCUMENTS’.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:87991) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:88697) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91929) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91931) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91932) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
There are 0 rows in 1 pages for object “MESTEMPLATEDOCUMENTS”.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS’ (object ID 1394416337).
CHECKTABLE fixed 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS’ (object ID 1394416337).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

6) Change DB back to Multi-User mode
USE [master]
GO
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

7) To verify the results we can run the DBCC Checktable(tablename) command again and if no issues results will be as below

Consistency_Erorrs_and_PFS_space

Posted in Database Corruption, MSSQL | Leave a comment

Modifying maxAllowedContentLength

Issue:

 “java.io.FileNotFoundException: http://dev-perfdb2/olap/msmdpump.dll” is thrown by an application due to default configuration in the IIS sever which throws an error when the content length of the data (the data which is passed from an application to DB is passed through the IIS) is too large.

Resolution: So to subdue this error we need to increase the maxAllowedContentLength on the IIS server where OLAP is setup
OS: Windows 2012
SSAS : SQL 2012
Web server: IIS 8.0

1) Open IIS Manager
2) Highlight “LocalHost”
3) Double click “Request Filtering”
4) Select “Hidden Segments” tab and right click “web.Config” and select “Edit Feature
Settings”, it opens up “Edit Request Filtering Settings” change “Maximum allowed
content length” to “4294967295(3.99GB)”
5) Restart IIS.

Posted in IIS, MSSQL | Leave a comment