Technology tips: December 2013

Wednesday, December 18, 2013

If you are assigned a task to reduce the log file size of sql server which is causing some space issues or because of this some other program may be interrupted or not working so you need to understand how log file typically ldf file behave and how its size can be shrunk.
If you want to do any operations on log file before doing that you have understand some other things which are important like recovery model.

What is recovery mode and why should I begin with Recovery Mode in SQL Server?
 Recovery mechanism in SQL Server tells- how log file can be used for recovery in the state of failure – like hardware failure or etc- in other word, from where you want sql server to start recovery.
There are 3 different recovery modes available in sql server
1)    Simple
2)    Full
3)    Bulk- Logged

Simply simple:
As name depicts – simple is for simple recovery – and you're telling SQL server that ok I can go ahead in the form of recovery and recover my data and my org can afford to lose little amount of data- this setting is ideal for test server or staging or development sql server but this is not ideal setting for “SQL Server Production”- how come you can set such rule for your organization that ok I am happy with losing little amount of data and recover from yesterday this kind of  rule is telling such setting for SQL server when you keep recovery mode simple . It’s transaction log duty to tell sql server to recover from where and just forget today.

Simply Full Recovery mode:
Full Recovery Mode tell sql server that you can recover transaction in any point in time – this mode also called point in time recovery – when any mishap happens to your data and because it has to recover from any point of time – so it always write transaction log in log file and that’s a big reason when transaction log file we called it ldf always grow in size.

Ideally you can take log backup as log file that always growing and if you can keep backup of that transaction log area and further you can automate this mechanism with sql server jobs task.
Default setting for recovery database is “Full as anything happens so it recovers transaction from any point in time. That was the reason why people should understand before shrinking log file.
If you are shrinking a ldf file and it causing error so probably you already have set recovery setting as simple so change to Full.

SSMS Shrinking ldf file:
SSMS will help you shrinking ldf file steps are simple as recovery option:
Tap into
Database>>Task>>Shrink >>Files
Select your log file to shrink by the way same steps will be taken if you are shrinking you DB file or mdf. Before doing this you select option and select recovery mode Full and do a shrink.
Shrinking is artistic work actually weather log shrinking or db shrinking.
If you are still having problem in shrinking log file so you execute the following sql statement – they are safe totally.

Shrink ldf log file using SQL Script:
But before that only thing is remaining that is the size of log file or ldf file while shrinking- you need to understand what size of the log file you have and how you want to manage it.
 According to  Microsoft,  there are different virtual log areas inside log file so to keep each and everything simple and safe – you can blindly provide no parameter for target size and “HOW” that will be listed down further.

Analyzing ldf or log file in sql server:
To check how big your log file size is- you run the following commands.
DBCC SQLPERF(LOGSPACE);
The result you would see after this-
Depicted picture is for informational purpose only - SSMS analyzing occupied space by log /ldf file


This result is telling you the log file size and percentage used by log file which is ldf.
You could see clearly the free space in log file. You have to reduce this file size and accommodate new log to be written. Ideally you can take backup.

You can use the use master command and run alter database statement and see if things are working then great if not and you have seen a error message telling this is not inside the current database. So use the USE command and set your database and then run alter database command

use master;
ALTER DATABASE [WSS_Content_ssp] SET RECOVERY SIMPLE WITH NO_WAIT
use master;
DBCC SHRINKFILE(WSS_Content_ssp_log,100)

use WSS_Content_MySite
ALTER DATABASE [WSS_Content_mysite] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(WSS_Content_mysite_log,10)

These sql scripts will help anyone who is trying to fix ldf file shrinking issue.

DBCC SHRINKFILE(WSS_Content_ssp_log, 200)
And finally run dbcc shrink command to shrink the log file, and see and analyze the size of the log file which is ldf.
Before running these scripts take log file backup and try flushing the log file - this is how people keep log backup and in this way when log file size increased would be decreased. You need to calculate mb which is occupied by log file and then run dbcc command in the example above - we have rough figure. Let's say you have 10000 mb of log file and 30% of that mb is being used and rest is empty so you can calculate the rest emply and mention that dbcc shrinkfile parameter - like 200 above.


Hope that you have fixed the increased log size issue in SQL server.




Wednesday, December 11, 2013

Recently I just come across with an error while I was attaching and detaching sql server database so gathered some point for everyone and surely they will help them.
If you are having issues in attaching and detaching sql server databases so there are number of reasons behind it you need to fix and troubleshoot them one by one.
The first step probably that you are detaching the database from sql server when you click on database and tap into task>detach then a dialog box will open saying database name and in the last column you would see a “link message” saying users connected with database so you can ask these users to disconnect from database or you can follow other procedure to disconnect users from the same database by force.
Simple option to disconnect users by force is to click drop connection option from the same dialog and click "ok" this will drop all connected users from database and you can do detaching database.
Another way include you can run scripts for detaching database, and you also can tap into activity monitor utility which is available when you click on sql server node and right click from sql server management studio let the utility load and tap into processes and select your database name and see activity details and  processes by right clicking on the process from there.
 If you are attaching database and facing issues so there are number of steps you can follow:
If you find error like the following:
Error Question:
______________________________________________
Error while opening mdf file
there is no editor for “path for your database and file”
 Make sure application for the file type is installed

______________________________________________
Error Answer:
The above error says – you have tried opening database file .mdf without SSMS.
Actually mdf file can be browsed when you will attach it to sql server database so you cannot just open it, first you need  to attach this mdf file to database using sql server management studio.

Attaching mdf file to sql server 
Load SQL Server Management Studio> Connect with sql server instance> select database and right click on it> click attach> browse your mdf file and click ok. You are done you would be seeing your database listed when you refresh the database node. 

You may be getting access denied error while opening mdf file.
_____________________________________________________________
Attach database failed for server “server name” . (Microsoft.SQLServer.smo)
Unable to open physical file “file location mdf”. Operating system error 5: “5(Access is denied.)”.(Microsoft SQL Server, Error: 5120)

_____________________________________________________________

Mostly there are permission issues when you are having error while attaching database to SQL server. This error also a permission issue on OS level.

Final and super option to fix this permission issue instead of going through number of other options and trying other credentials you just copy that mdf and ldf file from data folder of sql server to other location and try connecting them from other location , if mdf file is attached so you can copy back the mdf file to its location means data folder which is default folder for all databases by SQL server.

The above practice is based on SSMS 2008 R2.




Tuesday, December 10, 2013

Facebook is a giant in social network but recently Microsoft has revealed that has been working on a secret Project called So.cl, it’s a social channel by Microsoft like Facebook but Microsoft engineer has been doing work hard to make it a better social platform because this time Microsoft is playing with the master that is Facebook.
You can browse site directly by typing www.so.cl and start posting your status and other content. But Microsoft has decided to expand its audience through launching its application on android phone so it could go to other platform as well.
If you want to browse the site just provide your hotmail credentials or Facebook id if you have that already – no need to create it and that is it for going into it.
Major benefit of using this so.cl is you can create fully dynamic content and feature rich content instantly and share it among friends.
Google also has been penetrating to give a threat to Facebook but people like to spend more time with Facebook instead of other social platform, but this move looking quite impressive because Microsoft research has started this project long ago and they have been testing it among their own employee and now expanding it via mobile app.
You can also post little stories overlying text and images in funky style – they call it picotale.
 Blink feature will help you to create short dynamic media or video. If you are capturing a live event so just record it and post it here whereas Kodu functionality is for kids or a game platform and much more for kids- so you can browse so.cl

Defining Microsoft Social Media as So.cl:

A service by Microsoft penetrating in Social media space just need your hotmail account if you have that desire with hotmail id then you are done with using such a attempted social space may be competing with other Social service like google plus, facebook.
Just use it and tell us your experience.












Monday, December 9, 2013

When you want to upload a video file in flv format but that format is too big and SharePoint is not allowing you to upload that video on you portal so you have some options to fix such issues.
You can convert that video flv or flash video into .wmv format and then try uploading that video on portal when you convert that video into wmv format using tools available there so you would see format is wmv but file size is extremely reduced.
If you want to upload that video using content editor web-part so you can just upload that wmv resource which is reduced file size on your portal but again you might be facing one more issue there,  that video would not be displaying or playing automatically when user would visit your video page.
Actually you want a video player should be there to play that video inside portal when you upload that video using content editor webpart.
Here are few techniques that would help you to upload video from content query web-part easily and this technique would show/display video on portal page and play it accordingly.
You can try copying the following code in content editor web-part in html source area - just edit your sharepoint webpage and modify content editor web-part , you would see text area , from there you can select html code and write the following there:


<object classid="CLSID:22D6F312-B0F6-11D0-94AB-0080C74C7E95" width="600" height="400" id="mediaplayer1" title="Win Media Player">
  <param name="FileName" value="http://testserver /English/TS%20Strategy%20English%20ImageFilm2.wmv">
  <param name="AutoStart" value="False">
  <param name="ShowControls" value="False">
  <param name="ShowStatusBar" value="True">
  <param name="ShowDisplay" value="False">
  <param name="AutoRewind" value="False">
  <param name="DisplayBackColor" value="True" />
  <param name="BufferingTime" value="6">
  <embed src= http://testserver /English/TS%20Strategy%20English%20ImageFilm2.wmv" width="500" height="400" autostart="True" type="application/x-mplayer2"
pluginspage="http://www.microsoft.com/Windows/Downloads/Contents/MediaPlayer/" filename=" http://testserver /English/TS%20Strategy%20English%20ImageFilm2.wmv" showcontrols="True" showstatusbar="False" showdisplay="False" autorewind="True" displaybackcolor="True" bufferingtime="6">
</embed>
</object>

Note: Code is written by sqlservermanagementstudio.net



Object tag has classid attribute that is for media player class id so you don’t worry let it be as it is. You can change some other attributes if you want like auto play, buffertime etc.
You need to change the source file in value attribute of object tag.
Finally when you write this code in content editor weppart so you would see video is displaying and playing inside sharepoint portal.
We have assumed this you have uploaded video in sharepoint video library and just referring it here inside object tag.
You can leave any comment if you are having issues in fixing while uploading video in sharepoint using content editor webpart.