Moving SQL Server tempdb Files to a Different Location in SQL 2016

SQL Server performance improves if data, logs, tempDB data and temDB logs resides in separate drives. If data and logs are in same drive, after a certain period size of database TempDB grows and logical drive C may be short of space. In this example, default location is as shown in snapshot.

How to identify current location?

stmt:

select name, physical_name as currentlocation from sys.master_files where database_id = DB_ID(N'tempdb');

Results:

tempdev       S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb.mdf

templog         L:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\templog.ldf

temp2 S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb_mssql_2.ndf

temp3 S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb_mssql_3.ndf

temp4 S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb_mssql_4.ndf

..as seen using SQL (console) management studio

So, based on best practice let us move the tempdb data and logs from S drive and L drive to T and M drive respectively.

(please note that drive letters can vary, here in my environment I have used these letter for mapping drives)

As we have seen existing Path of tempdb data is at S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA  and existing Path of tempdb log is at L:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data

Let us move tempdb data to T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data  and tempdb log to M:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data

Statement:

Use master;

Alter database tempdb modify file (name = tempdev, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb.mdf');

Alter database tempdb modify file (name = templog, filename = 'M:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\templog.ldf');

Alter database tempdb modify file (name = temp2, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb_mssql_2.ndf');

Alter database tempdb modify file (name = temp3, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb_mssql_3.ndf');

Alter database tempdb modify file (name = temp4, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb_mssql_4.ndf');

Results:

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "temp2" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "temp3" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "temp4" has been modified in the system catalog. The new path will be used the next time the database is started.

Now Restart SQL server services for PINTRAAPPS instance

It is always good to delete orphaned data, so Delete Tempdb.mdf and Templog.ldf files  from old location (S & L drive).

I hope the above makes sense.

Addiction to Electronic Gadgets, Speech by Tejas Rinith

Global Warming

Remotely lock a computer and restrict them from logging back in

For investigative purpose, sometimes you have to freeze a terminal by throwing someone off a terminal, but at the same time preserve the evidence on the terminal. Let’s assume that if someone is using a terminal to send frustrating messages to their colleagues or having obscene chats or even an anonymous trying to hack, and you need to secure the running terminals to capture the commands that has been run.

Following process demonstrates that it is quite simple to accomplish such simple investigative task, provided you have admin privilege over the network:

1. It is mandatory to change the target account’s AD password. This ensures the culprit is deprived from logging back in
2. If you don’t have psexec, download the PSTools and extract the content and then target the terminal with psexec and use rundll32 to execute user32.dll with the LockWorkStation function. This will trigger the account lock. The following command can be tweaked for your purposes: H:\PSTools>PsExec.exe \\192.168.100.10 -d -u ventej\Administrator -i cmd /c "rundll32 user32.dll, LockWorkStation"
3. Now it’s time to seize the terminal. Make sure you are standing by ready for this, as the victim could be distressed and shut down his workstation, essentially removing evidence.

Good Luck!

Exchange 2013 Maintenance: Logs

Take a full backup. Once you take full backup all your logs will vanish. Else what?

 

Circular logging should not be enabled in a production environment. Circular logging are manually enabled only if you have noticed that the Exchange logs are enormously growing due to the following: either the nodes are unhealthy or there wasn't any successful exchange full backup.

 

To clear unwanted or orphaned logs, apart from enabling circular logging, it is also safe to dismount the Exchange DBs and mount back again. However, before doing it make sure to have a successful Exchange backup.

 

Exchange 2013:

 

Step1: ECP>>Servers>>Databases>>select required DB>>Check Active and Passive server's DB health. should be healthy. Double Click DB>>Maintenance>>Enable/Disable Circular logging. (Should not be enabled unless and otherwise required)

 

Step2: ECP>>Servers>>Databases>>select required DB>>Check Active and Passive server's DB health. should be healthy>>click more (3 dots...)>>Dismount. make sure the process completes successfully>> Mount the DB back again. And make sure the DB status shows mounted and healthy.

 

What if the log drive is full and exchange is down?

 

It is not a good idea to enable circular logging on a mailbox sever. As you may be aware that log drive should not be on a system drive, recommended to have a dedicated drive. Capacity planning is required for at least a period of 2-3 years both for SBM and large enterprise level org.

 

Consider moving the log files from the log drive to a temporary storage until the backup issue gets resolved. Once you have enough space, enable circular logging. Uncheck this feature after making sure there is enough log drive space.

 

In a nutshell circular logging recycles the logs.  Exchange relies on transaction or write-ahead logs to store events before they are committed to the database.  When the defined logs have been filled up, circular logging assumes that the first log must have been committed and recycles the logs to save disk space.

 

Check Which Domain Controller You Are Connected To

From Command Prompt Type "set l". Review the info next to the LOGONSERVER field to see the name of the domain controller.

 

Using Nltest Command, Type "nltest /dsgetdc:[FQDN]" (without quotation marks) into the console. Review the information next to the DC field to see the name of the domain controller with which the computer or system is communicating

 

हिन्दी में विज्ञापन ((Vigyapan Lekhan - Advertisement)

For study purpose only - for children

हिन्दी में विज्ञापन ((Vigyapan Lekhan - Advertisement):

1. अपना मकान का ऊपरी हिस्सा किराये पर (upper portion of house for rent)
'मकान का ऊपरी हिस्सा किराये पर है। साफ, सुथरी, बढ़िया शांत बस्ती में। 3 बैडरूम, 2 बाथरूम, ड्राइंग, डायनिंग, हवादार किचन, स्टडी, बड़ा बरामदा, गैराज। मकान के सामने हरा-भरा पार्क है। डी.पी.एस स्कूल दोहा से १० मिनट की दूरी परसंपर्क करें : फोन नं. ९९८०८०९०९०'

2. शीतल पेय का विज्ञापन (cold drinks)
नीबू का पानी
नीबू का रस 1 भाग, शकर 4-6 भाग लेकर दोनों को पकाकर चाशनी बनने पर आवश्यकतानुसार अल्प मात्रा में लौंग एवं कालीमिर्च का चूर्ण डालें, आवश्यकतानुसार पानी मिलाकर पीने से यह ग्रीष्म ऋतु में भोजन के प्रति रुचि उत्पन्न करता है, भोजन को पचाता है एवं गर्मी में वात बढ़ने से वात को नष्ट करता है।
OR
गुलाब शर्बत
गुलाब जल में दुगुनी शकर मिलाकर चाशनी बनाकर छान लें, 10-40 एमएल मात्रा तक पानी मिलाकर सेवन करने से शरीर में जलन, अधिक प्यास को नष्ट करता है, शरीर शीतल होता है।

3. डिब्बाबंद खाने की कंपनी के लिए विज्ञापन (canned/ closed container foods)

उत्पाद/सेवा (हम बेच):
डिब्बाबंद मशरूम, डिब्बा बंद भोजन, डिब्बाबंद मशरूम, डिब्बाबंद टमाटर का पेस्ट, सेब का रस ध्यान, डिब्बाबंद फल, डिब्बाबंद मछली, डिब्बाबंद मीठी मकई
देश/क्षेत्र: चीन (महाद्वीप)
व्यवसाय प्रकार: निर्माता, व्यापारिक कंपनी

4. चिप्स बनाने वाली कंपनी को कुशल कारीगरों की आवश्यकता है (experienced manpower required for chips making company)

आलू चिप्स कंपनी मास्टर आवश्यकता है
वेतन ४००० - ६०००
कंपनी - हल्दीराम चिप्स

Quick Activation of Server 2016 via Command Line

Having problems activating Windows Server 2016, (or previous versions Windows 8, or Server 2012) one of these three solutions below should get you through:

1.    Command Line to launch activation GUI:

This is handy if the GUI won’t start and you want to skip some steps to get it to work.

1. Click START – type RUN
2. Type slui 3 and press ENTER
   1. yes, SLUI: which stands for SOFTWARE LICENSING USER INTERFACE
      1. SLUI 1 brings up the activation status window
      2. SLUI 2 brings up the activation window
      3. SLUI 3 brings up the CHANGE PRODUCT KEY window
      4. SLUI 4 brings up the CALL MICROSOFT & MANUALLY ACTIVATE window
3. Type in your product key

     2.     To Activate Windows via Command Line:

   

 1.  Launch a CMD as an Administrator

 2.   Type: slmgr.vbs /ipk xxxxx-xxxxx-xxxxx-xxxxx-xxxxx

 3.   Press Enter

If your key is valid and you are connected to the internet, it should activate within a second or two.

 3.     If above steps points failure, then call Microsoft Activation Center; direct support line at 1 800-936-4900

 

Granting Calendar Access via Exchange Control Panel (ECP)

Ex: Add-MailboxFolderPermission -identity "Managingdirector:\Calendar" -user "personalassistant" -AccessRights Reviewer

 

To change permissions on a Calendar item, type the following command:

 

This command will grant Calendar access, with level reviewer to his Personal Assitant. You can use Editor instead of Reviewer for more access.

 

If Managing director had PA in the past, you may want to remove that access. To that you can use the following command:

 

Ex: Remove-MailboxFolderPermission -identity "ManagingDirector:\Calendar" -user "oldPA"

Desktop Wallpaper Group Policy doesn't work for Win 10 clients: workaround

 

Windows 10 does funky things with wallpapers. It likes to make a cached copy in  %appdata%\roaming\microsoft\windows\themes\cachedfiles

and it will sometimes make a copy under  %AppData%\roaming\Microsoft\Windows\Themes\TranscodedWallpaper.jpg as well.

 

There is different behavior on Windows 7 and Windows 10 on desktop wallpaper. In Windows 7, when we log into the system, the cached wallpaper file will re-generated automatically.

But in Windows 10, if the wallpaper path didn’t change, the cached wallpaper will not re-generate.

After I deleted below file and log off/log on I can see the desktop wallpaper automatically changed on Windows 10.

 

%USERPROFILE%\AppData\Roaming\Microsoft\Windows\Themes\TranscodedWallpaper

So I applied this deletion operation to the domain controller as a log off  script, the script which is as below.

I created a .bat file and the content is:

del %USERPROFILE%\AppData\Roaming\Microsoft\Windows\Themes\TranscodedWallpaper

 

Replication to all domains

Replicate to all domains via command prompt:

repadmin /syncall /AePD

Quickly replicate to Azure AD via Azure ADConnect using PowerShell from Azure AD Connect server (if you have hybrid mode)

Start-ADSyncSyncCycle

  

via Replmon GUI: