Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Saturday, 18 February 2017

New File growth effect Primary MDF and Secondary NDF Added in SQL Server

New File growth effect Primary MDF and Secondary NDF Added in SQL Server

s p_h e l p db db info

initially it was only 6 MB



Added some sample records to table on test db growth table


after added records you can see growth only on M D F file only


Now we added secondary file 

even if you decide to u n select on enable auto growth option on properties of db



it will give effect on auto growth as none


now we check s p_help db


now we insert sample data

it will give error as we disabled auto growth it will not allow data to insert as show error


we disabled auto growth so it will not allow data to insert see the error


So we should need to enable Auto growth on M D F then we are going to set auto growth option on
N D F then it will work as see below



s p_h e l p db of M D F and L D F info as see below


now we are going to alter database to new location as see below


take database offline then move the M D F file to new location as see below


Now move the database log file to new location as see below


after bring online database shows as new location F drive as see below


Now we insert some sample data as see below


Now we new insert data take effect on M D F and N D F as see below










Tuesday, 14 February 2017

How to MOVE database MDF and LDF file to New Drive location path step by step in SQL Server

Step 1

take data file path use sp_help db and save it to notepad and take screen shot and paste it to paint

Ex:

method 1

sp_helpdb 'databasename'

method 2

SELECT name, physical_name AS Current_Location
   FROM sys.master_files
          WHERE database_id = DB_ID(N'Testdbgrowth');

          GO

Step 2


Change file path through ALTER DATABASE MODIFY FILE query

USE master;
GO
ALTER DATABASE db_name
MODIFY FILE (NAME = dbMDFlogicalname, FILENAME = 'E:\new_location\Datafile name.mdf');
GO
ALTER DATABASE db_name
MODIFY FILE (NAME = dbLDFlogicalname, FILENAME = 'E:\new_location\Logfilename.ldf');

GO

Step 3:

verify any users or active connections still using the database or not using s p_who 2 query if any connection exists change single user and multi user mode immediately  to close connections as see 

below


USE master;
GO

-- Put database into single user mode closing open connections
ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

-- Put it back into multi user mode
ALTER DATABASE db_name

SET MULTI_USER;



Step 4:

take offline for DB

Step 5:

copy physical file from source location to new location (don't rename if you should rename need to give same in alter query)

Step 6:

bring database online

Step 7:

verify s p_ h e l p d b command to new path available on M D F and L D F 


Demo:

Step 1:

we can see using 

s p _help db 'database name' to take the data file path 

or we can use  s y s.master_files to get database files info


or we can s p_help file to get this m d f and l d f info (note it will be work when we point db see below)



you can see the M D F available location see below


you can see the L D F available location see below



Kindly take it to notepad for future reference  or take it screenshot and save it on paint u r wish

Step 2

now we need to prepare query to take effect of new path location as see below

USE master;
GO
ALTER DATABASE Testdbgrowth
MODIFY FILE (NAME = Testdbgrowth, FILENAME = 'D:\SQL\Data file\Testdbgrowth.mdf');
GO
ALTER DATABASE Testdbgrowth
MODIFY FILE (NAME = Testdbgrowth_log, FILENAME = 'D:\SQL\Log\Testdbgrowth_log.ldf');

GO


now we need to execute to take effect

it clearly says it will effect when next db went online/offline


Step 3:

now we need check whether any db is any connection using or not to take db offline without any issue


Step 4

now we are going to take db offline


now it shows db offline status


Step 5:

now the time to move M D F  cut and past it to new path location


now the time to move L D F  cut and past it to new path location



Step 6:

now time to bring database online



after success message kindly refresh on server as see below



you can see Test db Growth db comes online as see below

Step 7:

also kindly verify the M D F and L D F path comes to new location as see below




Note:

1)If you rename  M D F or L D F but not give correct name in alter query 

2) if you alter query with new path location but not move M D F and L D F then you will get below error


if we any data file or log file not place correctly we could see following error

path not available to come online 



still show db in recovery pending cant come online due to path not available


you can see take online is disabled you should only take to offline mode only 



solution

if db was in recovery pending then check the data file path m d f and l d f available  then 

once again alter query with correct path and bring db offline and bring online






that is it keep blogging and reading ☺☺☺☺☺☺☺☺

Local admin access users can not access S S M S (SQL Server Management Studio)


Local admin access users can not access S S M S (SQL Server Management Studio)

 when a user is having local admin access he can’t able to log in to S S M S (SQL Server Management Studio). It will give log in error

If user needs to get access to S S M S (SQL Server Management Studio) he should need to get sysadmin access or part of built in administrator group or any sysadmin group of AD

Here you can test user 


He is part of Administrator Group as see below



But when he tried to log into SQL Management Studio he will get error as see below




Keep Blogging !!!!!!!!!!



Friday, 27 January 2017

How to Avoid/Escape a server reboot while installing SQL Server 2008 R 2 or SQL Installation Fail situation?

Avoid a server reboot while installing SQL Server 2008 R 2?


If you are in a situation that you need not want server to be rebooted after SQL Server 2008 R 2 install,then follow the below steps
You can bypass the “Restart computer” rule when installing SQL Server 2008 r 2 and work around this by following these steps:
1. Open Registry Editor. Click Start –> Run, input “reg edit” and press Enter.
2. Go to H KEY_LOCAL_MACHINE\SYSTEM\Current Control Set\Control\Session Manager, and delete any value in “Pending File Rename Operations” key.
Scenario:
if you want to install SQL Server 2008 R 2 but it is holding more applications we can not offered any down time at all that time if your installation of any setup file fails it asks restart we can not proceed further uninstall existing file or install SQL new setup file so we need to follow above steps to avoid restart system and we can just install SQL new setup file without uninstall existing file.

Here you can see some files fails due to setup file corrupted or file missing as P F B 
(Please Find Below)

so it requires restart of system



If you are in a situation that you need not want server to be rebooted after SQL Server 2008 R2 install,then follow the below steps
You can bypass the “Restart computer” rule when installing SQL Server 2008 r 2 and work around this by following these steps:
1. Open Registry Editor. Click Start –> Run, input “reg edit” and press Enter.
2. Go to H KEY_LOCAL_MACHINE\SYSTEM\Current Control Set\Control\Session Manager, and delete any value in “Pending File Rename Operations” key.



In the above Pending File Rename operations just select all and copy it to notepad (for just reference for future use) and remove all text and save it and close the reg edit

now you can continue with new file setup to proceed further it did not ask any restart system 




That is it

Keep Blogging and Reading

Saturday, 7 January 2017

Database Mirroring Real time Examples


Database Mirroring Real time Examples


We can't able to restore if Database is different in both source and destination

Real time examples:

1)DB different in both Principal and Mirror:

Source:

Source db: Test D M 02


Destination:

Destination db:

Test D M 01


both database different so we cant able to configure mirror both database should be in same database name

while you try to configure mirror it will give error as see below


2)We can't rename standby with read_only database:

you will get below error while you try to rename read_only database

if you try to rename read_only db Test D M 01 to Test D M 02 you will get error as see below




3)DB same in both Principal and Mirror with Standby with read_only mode:

Principal and Mirror with read_only mode so we cant able to restore if you try to configure you will get below error


It will asks read_write mode to configure correctly.



we should use below command to bring db back to normal (READ_WRITE)

RESTORE DATABASE DB_NAME WITH RECOVERY


after issued it back to read_write



but if database is read_only mode then we can use



ALTER DATABASE [Test R W test] SET READ_WRITE WITH NO_WAIT



After issued above T SQL we can back to normal(read_write)