-1

I am trying to find the space used by a database through the sqlcmd utility. I used sp_spaceused command to get the details of the database size. Now i want to store only the available size (i.e unallocated space) to a variable .

I think the output is not correctly formatted while pasting , i haved added an image for reference . out.txt

This is my batch script :

echo off
sqlcmd -E -S <machinename> -i findspaceused.sql -o out.txt
set /p delExit=Press the ENTER key to exit...:

This is my sql file :

USE KCC;
EXEC sp_spaceused @oneresultset = 1;

The output of the batch script :

Changed database context to 'KCC'.
database_name                                                                                                                    database_size      unallocated space  reserved           data               index_size         unused            
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ ------------------ ------------------ ------------------ ------------------
KCC                                                                                                                              16.00 MB           4.11 MB            3984 KB            1456 KB            1416 KB            1112 KB
jarlh
  • 42,561
  • 8
  • 45
  • 63
NANTHAN
  • 1
  • Probably `for /f "skip=2tokens=1-13" %%A in ('sqlcmd -E -S -i findspaceused.sql') do (echo %%A echo %%B ... echo %%J echo %%K&set "unallocated=%%D %%E")` should set `unallocated` to the unallocated space (also should `echo` the individual elements) – Magoo Aug 29 '23 at 06:31
  • 1
    I would suggest skip learning bat scripts and learn powershell or even python. – siggemannen Aug 29 '23 at 06:55
  • 3
    Why a batch file? If you use `sqlcmd` with `-h -1` to turn off headers you can write some T-SQL to only return the `unallocated` value. – AlwaysLearning Aug 29 '23 at 07:11
  • 2
    You can specify the required DB in `sqlcmd` like this `sqlcmd -d KCC`. Then you don't need the `USE KCC;` part in your script which is hiding the space result. But _PLEASE_ put down the CMD and learn powershell instead. – Nick.Mc Aug 29 '23 at 07:25
  • Thank you for your comments everyone ,I was able to resolve my issue. I will also start working on my powershell. – NANTHAN Aug 29 '23 at 11:24

0 Answers0